On May 26, 5:44 am, Jasper wrote:
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?