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




  • Avatar

    I've tried implementing this technque, but keep getting an error.  I'm running Excel 2007 in Windows 7.


    Sub Heat_AGL_MC()
        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
    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.


  • Avatar

    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.

Powered by Zendesk