Catch max time/iteration dialog box when using Excel Solver in VBA
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
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
精彩评论