Summary
This article illustrates how to use a callback function when controlling the standard Excel Solver programmatically through VBA. The callback function, which you write in VBA, allows you to get control and check conditions during the solution process.
If Show Iteration Results is checked in the Solver Options dialog, your callback function will be called on each iteration – taking the place of the regular Show Trial Solution message box – so you can track the progress of the solve (e.g. check the objective function or constraint values in the Excel worksheet). Your callback function will also be called each time the ESC key is pressed, and each time a limit on the solution process is reached (such as the Max Time Limit or Max Iteration Limit).
Note: Risk Solver Platform can also be called programmatically through VBA, either using our Traditional API (explained here) or a more powerful object oriented API. Please see the chapter, "VBA Object Model Reference", in the Risk Solver Platform Reference Guide under the "Evaluator Object" topic for more information.
Solution
When controlling the Solver through VBA, a user-defined function can be called whenever the Solver would normally pause the solution process, for example, on each iteration if "Show Iteration Results" is checked in the Solver Options dialog (Data > Solver > Options in Excel 2007/2010 or Tools > Solver > Options in Excel 2003); when the ESC key is pressed; or each time a limit on the solution process is reached. As with the Show Trial Solution dialog, users can choose to continue on with the solution process or stop the Solver immediately. Note: This function must not alter the formulas of the objective or the constraints or the values in the variable cells
To use VBA functions such as SolverSolve, your Visual Basic module must include a reference to the Solver add-in (Solver.xla). In Microsoft Excel, open the Solver Parameters dialog (Data > Solver > Options in Excel 2007/2010 or Tools > Solver > Options in Excel 2003), to ensure that standard Excel Solver add-in is loaded. Then (after closing the Solver dialog) press Alt + F11 to open the Visual Basic Editor, choose Tools > References... and make sure that the box next to Solver is checked.
SolverSolve ShowRef Argument
Here is an example of defining a callback function, and passing its name to the SolverSolve function via the argument ShowRef.
VBA Syntax
Function ShowTrial(Reason As Integer)
Msgbox Reason
ShowTrial = False
End Function
Sub Test
answer = SolverSolve(UserFinish:=True, ShowRef:="ShowTrial″)
End Sub
The argument Reason, which must be present, is an integer value from 1 to 5:
- Function called (on every iteration) because the Show Iteration Results box in the Solver Options dialog was checked, or function called because the user pressed ESC to interrupt the Solver.
- Function called because the Max Time limit in the Solver Options dialog was exceeded.
- Function called because the Max Iterations limit in the Solver Options dialog was exceeded.
- Function called because the Max Subproblems limit was exceeded.
- Function called because the Max Integer Sols limit was exceeded.
Your callback function must return True if Solver should stop (same as the Stop button in the Show Trial Solution dialog) or False if it should continue running (same as the Continue button).
Your VBA function can inspect the current solution values on the worksheet, or take other actions such as saving or charting the intermediate values. However, it should not alter the values in the variable cells, or alter the formulas in the objective and constraint cells, as this could adversely affect the solution process.
SolverSolve UserFinish Argument
SolverSolve also takes an argument UserFinish you can use to control what happens at the end of the solution process. When the solution process ends and the UserFinish argument is True, no final dialog is displayed. If UserFinish is False, the Solver Results dialog is displayed.
If a Solver problem has not been completely defined, SolverSolve returns the #N/A error value. Otherwise the solution process is started (and if the ShowRef argument is supplied, your callback function will be called). When the solution process ends, SolverSolve returns an integer value that indicates the stopping condition. The standard Excel Solver returns values from 0 to 13. See below for descriptions of each. (When SolverSolve is used to call Risk Solver Platform, additional integer values may be returned.)
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 linearity conditions required by this Solver engine 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 the maximum time limit has been reached. |
11 |
There is not enough memory available to solve the problem. |
13 |
Error in model. Please verify that all cells and constraints are valid. |
Comments
2 comments
I've tried implementing this technque, but keep getting an error. I'm running Excel 2007 in Windows 7.
------------------------------------------
Sub Heat_AGL_MC()
'
Sheets("Prediction").Select
'
SolverReset
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.0001, AssumeLinear _
:=False, StepThru:=True, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="J24", MaxMinVal:=3, ValueOf:="0", ByChange:="A24,C18,B43,A31,M37"
SolverAdd CellRef:=Range("C19"), Relation:=2, FormulaText:=0
SolverAdd CellRef:=Range("U37"), Relation:=2, FormulaText:=0
SolverAdd CellRef:=Range("Q43"), Relation:=2, FormulaText:=0
SolverAdd CellRef:=Range("C43"), Relation:=1, FormulaText:=0
SolverAdd CellRef:=Range("M38"), Relation:=2, FormulaText:=0
'
Results = SolverSolve(UserFinish:=True, ShowRef:="SolverIteration")
'
Select Case Results
Case 0, 1, 2, 3, 10
' 0, 1, 2 = solution found, keep final values
' 3 = max iteration limit reached
' 10 = max time limit reached
SolverFinish KeepFinal:=1
Case 4
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 4: The Set Cell values do not converge")
Case 5
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 5: Solver could not find a feasible solution")
Case 6
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 6: Solver stopped at user's request")
Case 7
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 7: The linearity conditions required by this Solver engine are not satisfied")
Case 8
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 8: The problem is too large for Solver to handle")
Case 9
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 9: Solver encountered an error value in a target or constraint cell")
Case 11
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 11: There is not enough memory available to solve the problem")
Case 13
SolverFinish KeepFinal:=2
MsgBox ("Solver Error 13: Error in model. Please verify that all cells and constraints are valid")
End Select
Calculate
End Sub
Function SolverIteration(Reason As Integer)
' Called on each solver iteration
Const SolverContinue As Boolean = False
Const SolverStop As Boolean = True
'
Select Case Reason
Case 1
' 1 = Step Thru "True"
SolverIteration = SolverContinue ' Continue
MsgBox ("Reason " & Reason)
Case 2, 3, 4, 5
' 2 = Max Time reached
' 3 = Max Iterations reached
' 4 = Max Subproblems limit was exceeded
' 5 = Max Integer Sols limit was exceeded
SolverIteration = SolverStop ' Stop
MsgBox ("Reason " & Reason)
End Select
End Function
-------------------------------------------------------------
The error I get is: The formula you typed contains an error. Macro error at cell [SOLVER.XLAM]Excel4Functions!A21
I've narrowed down that this error occurs while executing the SolverSolve function. And it seems to preclude the callback function, because after I continue past the error, I get the Trial Solution dialog box instead of it being automatically cleared by the callback function.
Any help is appreciated.
Thanks!
After some additional troubleshooting, I was able to eliminate the error by removing spaces from the Excel filename. The macro is working as expected now.
Please sign in to leave a comment.