Catching max iterations Stop of Solver in VBA

[Guest]

I would like to set up my macro so that if Solver does not converge at
maximum iterations, the Solver dialog is not displayed, and the macro
continues on with the best guess. Is there a way to turn off the dialog if
Solver doesn't converge?

 

[Dana DeLouis]

Hi. Here is a general outline of the code. Good luck...

Sub DEMO()
Dim Results As Long

'...YOUR CODE to set Solver ...

Results = SolverSolve(True)

Select Case Results
Case 0, 1, 2
' Found a solution
' Yeah! Your code to continue...
SolverFinish KeepFinal:=True
Case 3
' Stop chosen when the maximum iteration limit was reached.
' Your code here??
Case 10
' Stop chosen when the maximum time limit was reached.
' Your code here...
Case 7
' The conditions for Assume Linear Model are not satisfied.
' Your code to Un-select this option, and try Solver again...
Case 4
' The Set Target Cell values do not converge.
' Keep count of this error. Perhaps use Random
' Initial values, and try Solver again.
Case Else
' Most likely an Error. :>(
' Now what?
End Select

End Sub

HTH :>)

 

[msuryexcel]

Hi Dana:
I am stuck inthe same place for many many days.
I need the actual code for automatic user response to the Specifi
dialog box given by Solver at the "mAx iterations"
I used your suggestion of result = SOvlersolve -- but I am not sur
whta to write in the case 3 to make sovler continue/stop/cancel in th
dialog box

Your help is much appreciated
thanks
mand

 

[Dana DeLouis]

Hi. I understand that you expect to reach Max Iterations in your Solver.
I'm not clear on what you wish to do at this point. You normally get a
dialog box saying you have reached the Iteration limit.
You can also set the limit to its maximum value...

SolverOptions Iterations:=32768

HTH

 

[Guest]

This code does not eliminate the Solver dialog box being displayed when max
iterations is reached. Is there anything that can be done to NOT display the
box that says "Max iterations have been reached"?

 

[Dana DeLouis]

I would like to set up my macro so that if Solver does not converge at
Hi. You need an Aux function for that to work.
Here's part of a general outline that I use.

Sub Your_Main_Code()
Dim Results
Const Keep_Results = 1
Const Discard_Results = 2
Const AnswerReport = 1

' << ONE OF A FEW IMPORTANT CHECKS! >>
If InStr(1, ActiveWorkbook.Name, Space(1)) > 0 Then
MsgBox "IMPORTANT: Remove all SPACES from workbook name",
vbCritical
End
End If

' << YOUR MAIN SOLVER CODE HERE >>

SolverOptions Iterations:=10 'Your Value here
SolverOptions StepThru:=True
Results = SolverSolve(True, "SolverStepThru")

Select Case Results
Case 0, 1, 2
' Solver found a solution.
' Keep final values & generate answer report
SolverFinish KeepFinal:=Keep_Results,
ReportArray:=Array(AnswerReport)
Case 3
'Max Iterations was exceeded
SolverFinish Keep_Results ' OR Discard_Results
End Select
End Sub

Function SolverStepThru(Reason As Integer)
Const xContinue As Boolean = False 'Excel XP
Const xStopRunning As Boolean = True 'Excel XP

Select Case Reason
Case 3
'Max Iterations was exceeded
SolverStepThru = xStopRunning
End Select
End Function

Good luck. HTH. :>)
--
Dana DeLouis
Win XP & Office 2003

This code does not eliminate the Solver dialog box being displayed when
max
iterations is reached. Is there anything that can be done to NOT display
the
box that says "Max iterations have been reached"?

<snip>

 

[msuryexcel]

Hi Dana:
I am sorry to say that I have tried the three responses you have given
on this problem and none of them seem to work for me.

Simply stated: I have a VBA code that runs the solver in a loop.
Inside the loop there are situaitons when the solver reaches the MAX
TIME LIMIT (or MAX iterations). In understand your idea of using the
"REASON" as a case variable. In case when the reason =10, I do not
want the dialog box to show at all -- instead it should take the option
"STOP" and go to the next iteraiton of the loop. I am not able to do
this. I need help in writing the code that does something similar to
SolverSolve UserFinish:=True
Your idea of the
Function SolverStepThru(Reason As Integer)
did not work.
Any other suggestion or help will be greatly appreciated.

Thanks in advance
manda

 

[Dana DeLouis]

"REASON" as a case variable. In case when the reason =10, I do not

want the dialog box to show at all -- instead it should take the option

Hi. I believe you meant when the "Result" is 10...
10 indicates a Time limit was reached.
Don't feel too bad... This technique is not documented anywhere as far as I
know.

This "should" work ...

Sub Your_Main_Code()
Dim Results

' << YOUR MAIN SOLVER CODE HERE >>

SolverOptions Iterations:=10 'Your Value here
SolverOptions StepThru:=True
Results = SolverSolve(True, "SolverStepThru")

Select Case Results
Case 0, 1, 2
' Solver found a solution.
SolverFinish 1
Case 3, 10
SolverFinish 1 ' Keep Results, and move on...
End Select
End Sub

Function SolverStepThru(Reason As Integer)
Select Case Reason
Case 2, 3
SolverStepThru = True ' Will Abort
End Select
End Function

 

[Dana DeLouis]

Hi. A slight correction. Based on your information, use this version of
the function instead.
Please write back if you still have problems. [or if it works :>) ]

Function ShowTrial(Reason As Integer)
ShowTrial = Reason > 1
End Function

 

[Dana DeLouis]

Ahh! I'm having a bad moment.
Try using this function instead:

Function SolverStepThru(Reason As Integer)
SolverStepThru = Reason > 1
End Function