Catch max time / iteration dialog when using Excel Solver in VBA
I am using the built-in solver in Excel 2003 in a VBA loop to solve a number of different problems. Sometimes the solver reaches the maximum time or iteration limit, which causes a pop-up dialog box to appear asking if the user wants to continue, stop, or complete. In all cases, I want this to end and move on to the next line of the loop. This will prevent the user from sitting and answering every time.
Looks like someone hit it right away but failed: http://www.excelforum.com/excel-programming/483175-catching-max-iterations-stop-of-solver-in-vba.html
+2
source to share
1 answer
here's a sample solution:
it uses the SolverSolve PassThru method to call a function to process 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
+2
source to share