VBA CODE SOLVER TO RETURN VALUE

[Jasper]

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]

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

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]

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]

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

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]

"....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]

"....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

J-

Aren't you glad I told you...?

-S

 

[Jon Peltier]

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

 

[SteveM]

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
_______

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]

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