Description
Allows you to specify advanced options for your Solver model. This function and its arguments correspond to the options in the Solver Options dialog box.
Before you use this function, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box in the Available References box. If Solver.xla doesn't appear in the Available References box, click Browse, click Solver in the \Excel\Library\Solver folder, and then click OK.
Syntax
SolverOptions(maxTime, iterations, precision, assumeLinear, stepThru, estimates, derivatives, search, intTolerance, scaling)
maxTime
Optional. The maximum time (in seconds) Microsoft Excel will spend solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767.
iterations
Optional. The maximum iterations Microsoft Excel will use in solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767.
precision
Optional. A number between 0 and 1 that specifies the degree of precision to be used in solving the problem. The default precision is 0.000001. A lower precision is indicated if there are fewer decimal places - for example, 0.0001. In general, the higher the precision you specify (a smaller number), the more time Solver will take to reach solutions.
assumeLinear
Optional. If True, Solver assumes that the underlying model is linear. This speeds the solution process, but it should be used only if all the relationships in the model are linear. False is the default.
stepThru
Optional. If True, Solver pauses at each trial solution. You can pass Solver a macro to run at each pause by using the showRef argument of the SolverSolve function. If False, Solver doesn't pause at each trial solution. False is the default.
estimates
Optional. Specifies the approach used to obtain initial estimates of the basic variables in each one-dimensional search: 1 represents Tangent estimates, and 2 represents Quadratic estimates. Tangent uses linear extrapolation from a tangent vector. Quadratic uses quadratic extrapolation; this may improve the results on highly nonlinear problems. Tangent is the default.
derivatives
Optional. Specifies forward differencing or central differencing for estimates of partial derivatives of the objective and constraint functions: 1 represents forward differencing, and 2 represents central differencing. Central differencing requires more worksheet recalculations, but it may help with problems that generate a message saying that Solver couldn't improve the solution. With functions whose graphical representations aren't smooth and continuous, you should use the Central differencing option. Forward differencing is the default.
search
Optional. Use the Search options to specify which search algorithm will be used at each iteration to decide which direction to search in: 1 represents the Newton search method, and 2 represents the Conjugate search method. Newton, which uses a quasi-Newton method, is the default search method. This method typically requires more memory than the Conjugate search method, but it requires fewer iterations. Conjugate gradient searching requires less memory than the Newton search method, but it typically requires more iterations to reach a particular level of accuracy. You can try this method if you have a large problem and memory usage is a concern. Conjugate searching is especially useful if stepping through the iterations reveals slow progress between successive trial points.
intTolerance
Optional. A decimal number between 0 and 1 that specifies the integer tolerance. This argument applies only if integer constraints have been defined. You can adjust the Tolerance figure, which represents a percentage of error allowed in the optimal solution when an integer constraint is used on any element of the problem. A higher tolerance (allowable percentage of error) would tend to speed up the solution process.
scaling
Optional. If True, and if two or more constraints differ by several orders of magnitude, Solver scales the constraints to similar orders of magnitude during computation. This is useful when the inputs ( in the By Changing Cells box in the Solver Parameters dialog box) and outputs (in the Set Target Cell and Subject To The Constraints boxes in the Solver Parameters dialog box) have large differences in magnitude - for example, maximizing percent profit based on million-dollar investments. If False, Solver calculates without scaling the constraints. False is the default.
See Also
SolverOk Function.
Example
This example sets the Precision option to .001.
Worksheets("Sheet1").Activate
SolverReset
SolverOptions precision:=0.001
SolverOK setCell:=Range("TotalProfit"), _
maxMinVal:=1, _
byChange:=Range("C4:E6")
SolverAdd cellRef:=Range("F4:F6"), _
relation:=1, _
formulaText:=100
SolverAdd cellRef:=Range("C4:E6"), _
relation:=3, _
formulaText:=0
SolverAdd cellRef:=Range("C4:E6"), _
relation:=4
SolverSolve userFinish:=False
SolverSave saveArea:=Range("A33")