MS Office Forum / Excel / Programming / May 2008
VBA CODE SOLVER TO RETURN VALUE
|
|
Thread rating:  |
Jasper - 26 May 2008 10:44 GMT Hey there everybody,
while designing a statistical procedure, I was tempted to use Excel 97, for convinience of extreme oversight. However, I need to solve 16,000 regressions. Not terribelly hard stuff, though the VBA code I designed does not return any value, or keep the value. This is the code I use, can someone see where things go wrong? Thanks in advance!
Sub test() SolverReset SolverOk SetCell:="$DV$4, MaxMinVal:=2, ValueOf:=1", ByChange:="$DV$4" SolverSolve UserFinish:=False 'Changing userfinish from false to true does not yield any difference SolverFinish KeepFinal:=1 'Changing this line into SolverFinish KeepFinal:=1, ReportArray:=1, will only yield an error on the lack of availability of memory End Sub
SteveM - 26 May 2008 13:23 GMT > Hey there everybody, > [quoted text clipped - 13 lines] > only yield an error on the lack of availability of memory > End Sub Jasper,
It is not clear to me what you are trying to do. It appears that you are setting the objective function equal to a single variable. But they refer to the same cell which won't work.
When you say "return a value" that is not clear to me either as to what value you want returned. To return the solution status of a model, you simply declare an integer variable and set that equal to the SolverSolve function. e.g.,
Dim retVal as Integer retVal = SolverSolve(UserFinish:=False) 'False if you want the Solver completion dialog window to appear MsgBox retVal
The last point I can make it is that if you mean by 16,000 regressions you have 16,000 variables then your model exceeds the variable capacity of the Premium Solver Platform which is 8000 variables. So unless you've bought a third-party solver like MOSEK or XPRESS, you can't solve a problem that large using Frontline. And if you do have a copy of MOSEK or XPRESS you probably don't want to be using Frontline for model management anyway.
SteveM
P.S. You can do linear regression via LP, but with the statistics packages available why would you?
Jasper - 26 May 2008 14:37 GMT Hello Steve,
sorry for the typo, should refer to an adjacent cell, which is a product matrix of error-terms. The reason for using Excel in this case is to be able to show what I've done in detail to my thesis supervisor. And the ability that, of lined up, little changes can be easily made and tracked down in Excel. Thanks for the heads up on analytical software nonetheless.
These 16,000 regressions will stand apart from each other so will not interfere in any kind. It just a simple routine, as described, that needs, in due time, 16,000 repetitions. Something I'm not eager to do manually as one can imagine. ;)
My knowledge and skill in VBA has, by not using it for 3years, become a bit rusty. So trying to implement your suggestion, as it is indeed what I was looking for, I just want the solution found by the solver to be placed in DU4, well it goes like following:
Sub test() SolverReset SolverOk SetCell:="$DV$4, MaxMinVal:=2, ValueOf:=1", ByChange:="$DU$4" Dim retVal As Integer 'I believe one first has to specify something to be integer followed by the direction on what the value of the integer is, in this case solversolve retVal = SolverSolve(UserFinish:=True) 'as I don't have a need to see the dialog MsgBox retVal SolverFinish KeepFinal:=1 End Sub
Alas, I get the "Types do not match" I'm gonna try to find out why, though as anybody has got any suggestion as to why, I'm open to that. Thank you so far!
Jasper
SteveM - 26 May 2008 15:23 GMT > Hello Steve, > [quoted text clipped - 31 lines] > > Jasper Jasper,
Now you really have me confused as to what your intent is.
First, retVal is not the solution value of the model, it is the solver status value. I.e., what kind of solution was delivered. E.g., feasible, infeasible, unbounded, etc. Frontline now hides their documentation link and makes it available only to licensed users. But that contains the return codes for solver. You may be able to do a web search and track them down someplace else.
So back to your model itself. It still doesn't make sense. The the model solution value is your SetCell. So you have that defined. The ByChange value is supposed to be the range of decision variables. If you are only defining a single cell as a single decision variable, there's not much of a problem. Especially because you are not defining any constraint sets.
Which leads me to ask about your 16,000 repetitions. Are you actually looking to do a simulation with 16,000 repetitions? Because if you are, you are using the wrong platform. Solver is an optimization engine not a simulation engine.
If you do indeed have a simulation problem, and the model is pretty simple, you could code up a 16,000 iteration model in VBA using Excel's built-in Random function. Or to make your life easier, you could download a trial version of a simulation software package like@Risk or Crystal Ball and just try to get all your model runs in before the trial period runs out. Although I would guess that your university has copies of spreadsheet simulation software available for student use.
SteveM
Jasper - 26 May 2008 15:48 GMT "....The model solution value is your SetCell. So you have that defined. The ByChange value is supposed to be the range of decision variables. If you are only defining a single cell as a single decision variable, there's not much of a problem..."
There's the crux, the code works fine, though for some reason the result will not show in the designated place after the macro is done. Though I ran out of things that could be wrong about this code.
SolverReset SolverOk SetCell:="$DV$4, MaxMinVal:=2, ValueOf:=1", ByChange:="$DU$4" SolverSolve UserFinish:=True SolverFinish KeepFinal:=1
Thanks so far, I'm looking into Chrystal Ball. Gr.
Jasper
SteveM - 26 May 2008 22:44 GMT > "....The model solution value is your SetCell. So you have that defined. The > ByChange value is supposed to be the range of decision variables. If [quoted text clipped - 13 lines] > > Jasper J-
Aren't you glad I told you...?
-S
Jon Peltier - 26 May 2008 22:44 GMT Your line was missing some quotes: SolverOk SetCell:="$DV$4", MaxMinVal:="2", ValueOf:="1", ByChange:="$DU$4" Off the top of my head, I don't know whether they are needed around the numerical values.
I've had a little experience with Crystal Ball and with another package, @Risk ("at risk") by Palisade. My experience is that the Palisade product interfaces more smoothly with Excel. You should at least look at both.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______
> "....The model solution value is your SetCell. So you have that defined. > The [quoted text clipped - 15 lines] > > Jasper SteveM - 27 May 2008 00:02 GMT On May 26, 5:44 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> wrote:
> Your line was missing some quotes: > SolverOk SetCell:="$DV$4", MaxMinVal:="2", ValueOf:="1", [quoted text clipped - 32 lines] > > > Jasper Jasper,
I agree with Jon. Crystal Ball does better marketing, but @Risk is a better product. And Palisade just released version 5 of their decision tools suite. So you can try that out simultaneously while solving your problem.
SteveM
Jerry W. Lewis - 28 May 2008 02:31 GMT Unless these regressions are nonlinear in the unknown parameters, then SOLVER is a very inefficient and potentially inaccurate way to do this.
For simple linear regressions, consider the worksheet functions SLOPE, INTERCEPT, RSQ, and STEYX
For multiple regression, consider the worksheet functions LINEST or LOGEST.
Jerry
> Hey there everybody, > [quoted text clipped - 13 lines] > only yield an error on the lack of availability of memory > End Sub
|
|
|