Solver VBA - Defining Solver Options

[Kyle]

I'm using the basic version of solver that comes with excel. I've been
defining the constraints, variables etc using VBA macros as shown in article
843304. I now need to define the max time, number of iterations, precision
etc - basically the options that you can define from the actual Solver
Parameters window. Does anyone know how you can define these options within
VBA code? It isn't discussed in the aforementioned article. Is there
perhaps another article that discusses this material?

Thanks in advance!

 

[Jon Peltier]

I have a bunch of links at the bottom of this page which might help:

http://peltiertech.com/Excel/SolverVBA.html

Did you try recording a macro while you adjusted these parameters?

- Jon

 

[Steve]

Where can I find Article 843304?

Thanks!

 

[Dana DeLouis]

Where can I find Article 843304?

Microsoft Help gets worse by the year...
Here's a simple macro I use...

Sub KB_Artilce_Number()
Const Kb As String = "http://support.microsoft.com/kb/#/en-us"
Dim strAdr As String

strAdr = InputBox("Enter KB article Number")
strAdr = Replace(Kb, "#", strAdr)

ActiveWorkbook.FollowHyperlink Address:=strAdr, NewWindow:=True
End Sub

 

[Kyle]

I'm not sure if this is the same link that Dana gave but here's the article:

http://support.microsoft.com/kb/843304

Alternatively, if this link doesn't work search for: How to create Visual
Basic macros by using Excel Solver in Excel 97 in a google search, its the
first link.

 

[Kyle]

Thanks for your help Jon, I accidentaly found a way of setting the options
when I tried to save a model within the worksheet and then record the upload
of that scenario using a macro. Here's the vba code template for anyone
interested:

SolverOptions MaxTime:=32767, Iterations:=32767, Precision:=0.00001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, _
AssumeNonNeg:=False

Its fairly clear what the code refers to within the solve options window.
The only options I've changed from the default are setting the max time and
iterations to max and perhaps altering the precision.