Skip to main content

Using a Callback Function when Calling the standard Excel Solver through VBA

Nicole Steidel
Updated

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:

  1. 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.
  2. Function called because the Max Time limit in the Solver Options dialog was exceeded.
  3. Function called because the Max Iterations limit in the Solver Options dialog was exceeded.
  4. Function called because the Max Subproblems limit was exceeded.
  5. 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. 

Related articles

Comments

2 comments

Date Votes
  • brian.t.vaniman

    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!

    0
  • brian.t.vaniman

    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.

    0

Please sign in to leave a comment.

Powered by Zendesk