![[Home]](images/bhome.gif){kind=small}
![[What's New]](images/bnews.gif){kind=small}
![[Products & Services]](images/bprdsrv.gif){kind=small}
![[Contents]](images/btoc.gif){kind=small}
![[Feedback]](images/bfeed.gif){kind=small}
![[Search]](images/bsrch.gif){kind=small}
![[Dividing Line Image]](div.gif){kind=small}
This page documents the functions available to control the Solver in the Microsoft Excel macro language and in Visual Basic Applications Edition (VBA). If you are starting a new project, you will almost certainly find the VBA functions preferable.
The Microsoft Excel add-in SOLVER.XLA supplies the interface to the Solver functions for both VBA and the macro language (as well as the interactive user interface to the Solver). This add-in must be installed in order for you to use the Solver. If the Solver... choice appears on the Tools menu, the add-in is installed.
To use the VBA functions, your module must include a reference to the SOLVER.XLA add-in. Choose Tools References... and make sure that the check box next to SOLVER.XLA is checked. The macro language functions may be used directly, as long as SOLVER.XLA is installed.
The functions are listed alphabetically. If your browser supports bookmarks, you can click on the names in the Function Directory to jump to the description of each function.
Equivalent to choosing Solver... from the Tools menu and choosing the Add button in the Solver Parameters dialog box. Adds a constraint to the current problem.
VBA Syntax
SolverAdd(CellRef:=, Relation:=, FormulaText:=)
Macro Language Syntax
=SOLVER.ADD(cell_ref, relation, formula)
CellRef is a reference to a cell or a range of cells on the active worksheet and forms the left hand side of the constraint.
Relation specifies the arithmetic relationship between the left and right sides, or whether CellRef must have an integer value at the solution.
Relation Relationship 1 <= 2 = 3 >= 4 Int (CellRef is an integer variable)
FormulaText is the right hand side of the constraint and will often be a single number, but it may be a formula or a reference to a range of cells.
If Relation is 4, FormulaText is ignored, and CellRef must be a subset of the By Changing Cells.
If FormulaText is a reference to a range of cells, the number of cells in the range must match the number of cells in CellRef, although the shape of the areas need not be the same. For example, CellRef could be a row and FormulaText could refer to a column, as long as the number of cells is the same.
Remarks
Equivalent to choosing Solver... from the Tools menu and choosing the Change button in the Solver Parameters dialog box. Changes the right side of an existing constraint.
VBA Syntax
SolverChange(CellRef:=, Relation:=, FormulaText:=)
Macro Language Syntax
=SOLVER.CHANGE(cell_ref, relation, formula)
For an explanation of the arguments and constraints, see SolverAdd.
Remarks
Equivalent to choosing Solver... from the Tools menu and choosing the Delete button in the Solver Parameters dialog box. Deletes an existing constraint.
VBA Syntax
SolverDelete(CellRef:=, Relation:=, FormulaText:=)
Macro Language Syntax
=SOLVER.DELETE(cell_ref, relation, formula)
For an explanation of the arguments and constraints, see SolverAdd.
Remarks
Equivalent to selecting options and clicking OK in the Solver Results dialog box that appears when the solution process is finished. The dialog box will not be displayed.
VBA Syntax
SolverFinish(KeepFinal:=, ReportArray:=)
Macro Language Syntax
=SOLVER.FINISH(keep_final, report_array)
KeepFinal is the number 1 or 2 and specifies whether to keep or discard the final solution. If KeepFinal is 1 or omitted, the final solution values are kept in the changing cells. If KeepFinal is 2, the final solution values are discarded and the former values of the changing cells are restored.
ReportArray is an array argument specifying what reports to create when Solver is finished.
If ReportArray is Microsoft Excel creates
{1} An Answer Report
{2} A Sensitivity Report
{3} A Limit Report
A combination of these values produces multiple reports. For example, if ReportArray = {1,2}, Microsoft Excel creates an answer report and a sensitivity report. Use the Array function in VBA to create an array of constant values.
Equivalent to selecting options in the Solver Results dialog box that appears when the solution process is finished. The dialog box will be displayed, and the user will be able to change the options you initially specify.
VBA Syntax
SolverFinishDialog(KeepFinal:=, ReportArray:=)
Macro Language Syntax
=SOLVER.FINISH?(keep_final, report_array)
For an explanation of the arguments, see SolverFinish.
Returns information about the current Solver problem. The settings are specified in the Solver Parameters and Solver Options dialog boxes, or with the other Solver functions described on this page.
VBA Syntax
SolverGet(TypeNum:=, SheetName:=)
Macro Language Syntax
=SOLVER.GET(typenum, sheet_name)
TypeNum is a number specifying the type of information you want. The following settings are specified in the Solver Parameters dialog box.
TypeNum Returns
1 The reference in the Set Cell box, or the #N/A error value if
Solver has not been used on the active document
2 A number corresponding to the Equal To option
1 = Max
2 = Min
3 = Value Of
3 The value in the Value Of box
4 The reference in the By Changing Cells box
5 The number of constraints
6 An array of the left sides of the constraints in the form of text
7 An array of numbers corresponding to the relationships between
the left and right sides of the constraints:
1 = <=
2 = =
3 = >=
4 = int
8 An array of the right sides of the constraints in the form of text
The following settings are specified in the Solver Options dialog box:
TypeNum Returns
9 The maximum calculation time
10 The maximum number of iterations
11 The precision (as a decimal number)
12 The integer tolerance value (as a decimal number)
13 TRUE if Assume Linear Model or Assume LP/QP Model check box is
selected; FALSE otherwise
14 TRUE if Show Iteration Result check box is selected; FALSE
otherwise
15 TRUE if Use Automatic Scaling check box is selected; FALSE
otherwise
16 A number corresponding to the type of estimates:
1 = Tangent
2 = Quadratic
17 A number corresponding to the type of derivatives:
1 = Forward
2 = Central
18 A number corresponding to the type of search:
1 = Newton
2 = Conjugate
SheetName is the name of a worksheet that contains the Solver problem for which you want information. If SheetName is omitted, it is assumed to be the active sheet.
Equivalent to choosing Solver... from the Tools menu, choosing the Options button from the Solver Parameters dialog box, and choosing the Load Model button in the Solver Options dialog box. Loads Solver problem specifications that you have previously saved on the worksheet.
VBA Syntax
SolverLoad(LoadArea:=)
Macro Language Syntax
=SOLVER.LOAD(load_area)
LoadArea is a reference on the active worksheet to a range of cells from which you want to load a complete problem specification.
The first cell in LoadArea contains a formula for the Set Cell Box; the second cell contains a formula for the changing cells; subsequent cells contain constraints in the form of logical formulas. The last cell optionally contains an array of Solver option values (see SolverOptions).
Although LoadArea must be on the active worksheet, it need not be the current selection.
Equivalent to choosing Solver... from the Tools menu and specifying options in the Solver Parameters dialog box. Specifies basic Solver options. The dialog box will not be displayed.
VBA Syntax
SolverOk(SetCell:=, MaxMinVal:=, Valueof:=, ByChange:=)
Macro Language Syntax
=SOLVER.OK(set_cell, max_min_val, value_of, by_changing)
SetCell corresponds to the Set Cell box in the Solver Parameters dialog box (the objective function in the optimization problem). SetCell must be a reference to a cell on the active worksheet. If you enter a cell, you must enter a value for MaxMinVal. In the macro language syntax, if you do not enter a cell, you must include three commas before the ByChanging value.
MaxMinVal corresponds to the options Max, Min and Value Of in the Solver Parameters dialog box. Use this option only if you entered a reference for SetCell.
MaxMinVal Option specified 1 Maximize 2 Minimize 3 Match specific value
ValueOf is the number that becomes the target for the cell in the Set Cell box if MaxMinVal is 3. ValueOf is ignored if the cell is being maximized or minimized.
ByChanging indicates the changing cells (decision variables), as entered in the By Changing Cells box. ByChanging must be a cell reference (usually a cell range or multiple reference) on the active worksheet.
Equivalent to choosing Solver... from the Tools menu and specifying options in the Solver Parameters dialog box. The Solver Parameters dialog box will be displayed, and the user will be able to change the options you initially specify.
VBA Syntax
SolverOkDialog(SetCell:=, MaxMinVal:=, Valueof:=, ByChange:=)
Macro Language Syntax
=SOLVER.OK?(set_cell, max_min_val, value_of, by_changing)
For an explanation of the arguments, see SolverOk.
Equivalent to choosing Solver... from the Tools menu and then choosing the Options button in the Solver Parameters dialog box. Specifies the Solver algorithmic options.
VBA Syntax
SolverOptions(MaxTime:=, Iterations:=, Precision:=, AssumeLinear:=, StepThru:=, Estimates:=, Derivatives:=, SearchOption:=, IntTolerance:=, Scaling:=)
Macro Language Syntax
=SOLVER.OPTIONS(max_time, iterations, precision, assume_linear, step_thru, estimates, derivatives, searchoption, int_tolerance, scaling)
The arguments correspond to the options in the Solver Options dialog box. If an argument is omitted, the Solver maintains the current setting for that option. If any of the arguments are of the wrong type, the function returns the #N/A error value. If all arguments are of the correct type, but an argument has an invalid value, the function returns a positive integer corresponding to its position. A zero indicates that all options were accepted.
MaxTime must be an integer greater than zero. It corresponds to the Max Time box.
Iterations must be an integer greater than zero. It corresponds to the Iterations box.
Precision must be a number between zero and one, but not equal to zero or one. It corresponds to the Precision box.
AssumeLinear is a logical value corresponding to the Assume Linear Model check box. If FALSE, the Solver will use the GRG nonlinear solver. If TRUE, it will use the Simplex method linear solver.
StepThru is a logical value corresponding to the Show Iteration Results check box. If TRUE, Solver pauses at each trial solution; if FALSE it does not. If you have supplied SolverSolve with a valid VBA function or macro reference, your function or macro will be called each time Solver pauses; otherwise the standard Show Trial Solution dialog box will appear.
Estimates is the number 1 or 2 and corresponds to the Estimates option: 1 for Tangent and 2 for Quadratic.
Derivatives is the number 1 or 2 and corresponds to the Derivatives option: 1 for Forward and 2 for Central.
SearchOption is the number 1 or 2 and corresponds to the Search option: 1 for Newton and 2 for Conjugate.
IntTolerance is a number between zero and one, corresponding to the Tolerance box. This argument applies only if integer constraints have been defined.
Scaling is a logical value corresponding to the Use Automatic Scaling check box. If TRUE, then Solver rescales the constraints internally to similar orders of magnitude during computation. If FALSE, Solver calculates normally. In the standard Microsoft Excel Solver, this option affects the GRG nonlinear solver only.
Equivalent to choosing Solver... from the Tools menu and choosing the Reset All button in the Solver Parameters dialog box. Erases all cell selections and constraints from the Solver Parameters dialog box and restores all the settings in the Solver Options dialog box to their defaults. The SolverReset function is automatically performed when you call SolverLoad.
VBA Syntax
SolverReset()
Macro Language Syntax
=SOLVER.RESET()
Equivalent to choosing Solver... from the Tools menu, choosing the Options button from the Solver Parameters dialog box, and choosing the Save Model button in the Solver Options dialog box. Saves the problem specifications on the worksheet.
VBA Syntax
SolverSave(SaveArea:=)
Macro Language Syntax
=SOLVER.SAVE(save_area)
SaveArea is a reference on the active worksheet to a range of cells or to the upper-left corner of a column of cells into which you want to save the current problem specification.
Remarks
Equivalent to choosing Solver... from the Tools menu and choosing the Solve button in the Solver Parameters dialog box. If successful, returns an integer value indicating the condition that caused the Solver to stop, as described below.
VBA Syntax
SolverSolve(UserFinish:=, ShowRef:=)
Macro Language Syntax
=SOLVER.SOLVE(user_finish, show_ref)
UserFinish is a logical value specifying whether to show the standard Solver Results dialog box.
ShowRef is a VBA function or macro to be called in place of displaying the Show Trial Solution dialog box. It is used when you want to regain control whenever Solver finds a new intermediate solution value. Here is an example of defining and using the argument ShowRef:
sub test
answer=SolverSolve(True,"ShowTrial")
end sub
function ShowTrial(Reason as integer)
msgbox Reason
ShowTrial=true
end function
The argument Reason, which must be present, is an integer value from 1 to 3:
The function must return TRUE if the Solver should continue running (same as the Continue button in the Show Trial Solution dialog), or FALSE if it should stop (same as the Stop button).
Remarks
If a Solver problem has not been completely defined, SolverSolve returns the #N/A error value. Otherwise the Solver "engine" is started, and the problem specifications are passed to it. When the solution process is complete, SolverSolve returns an integer value indicating the stopping condition:
Value Stopping Condition
0 Solver found a solution. All constraints and optimality conditions
are satisfied.
1 Solver has converged to the current solution. All constraints are
satisfied.
2 Solver cannot improve the current solution. All constraints are
satisfied.
3 Stop chosen when the maximum iteration limit was reached.
4 The Set Cell values do not converge.
5 Solver could not find a feasible solution.
6 Solver stopped at user's request.
7 The conditions for Assume Linear Model are not satisfied.
8 The problem is too large for Solver to handle.
9 Solver encountered an error value in a target or constraint cell.
10 Stop chosen when maximum time limit was reached.
11 There is not enough memory available to solve the problem.
12 Another Excel instance is using SOLVER.DLL. Try again later.
13 Error in model. Please verify that all cells and constraints are
valid.
