Catch max time/iteration dialog box when using Excel Solver in VBA

Question

I am using the built-in solver in Excel 2003 within a VBA loop to solver a number of different problems. Occasionally, the solver hits the maximum time or iterations limit, which causes a pop-up dialog box to appear asking whether the user wants to Continue, Stop, or End. In all cases I want it to end, and proceed to the next line of the loop. This will prevent a user from having to sit there and respond each time.

It appears someone took a stab at it here, but failed: http://www.excelforum.com/excel-programming/483175-catching-max-iterations-stop-of-solver-in-vba.html

Answer 1

here's a sample solution:

it uses the SolverSolve PassThru method to call a function to handle the solver result at each iteration.

Option Explicit

Sub SolverExample()
    Dim results

    ' Set up your solver here...


    ' Execute solve
    SolverOptions StepThru:=True

    results = SolverSolve(True, "SolverIteration")

    Select Case results
    Case 0, 1, 2
        ' solution found, keep final values
        SolverFinish KeepFinal:=1
    Case 4
        'Target does not converge
        'Your code here
    Case 5
        'Solver could not find a feasible solution
        'Your code here
    Case Else
        'Your code
    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
        SolverIteration = False ' Continue

    Case 2
        ' Max Time reached
        SolverIteration = True ' Stop

    Case 3
        ' Max Iterations reached
        SolverIteration = True ' Stop

    End Select
End Function