Catching max iterations Stop of Solver in VBA

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?

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 :>)
--
Dana DeLouis
Win XP & Office 2003


"wgaskill" <[email protected]> wrote in message
news:[email protected]...
>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?

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

Your help is much appreciated
thanks
manda

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
--
Dana DeLouis
Win XP & Office 2003


"msuryexcel" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Dana:
> I am stuck inthe same place for many many days.
> I need the actual code for automatic user response to the Specific
> dialog box given by Solver at the "mAx iterations"
> I used your suggestion of result = SOvlersolve -- but I am not sure
> whta to write in the case 3 to make sovler continue/stop/cancel in the
> dialog box
>
> Your help is much appreciated
> thanks
> manda
>

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" wrote:

> 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 :>)
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "wgaskill" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

>> >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. ...

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


"wgaskill" <[email protected]> wrote in message
news:[email protected]...
>
> 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>

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

Originally Posted by 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 :>)
--
Dana DeLouis
Win XP & Office 2003


"wgaskill" <[email protected]> wrote in message
news:[email protected]...
>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?

> "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
Win XP & Office 2003


"msuryexcel" <[email protected]> wrote
in message news:[email protected]...
>
> 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 Wrote:
>> 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 :>)
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> "wgaskill" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>
>
> --
> msuryexcel
> ------------------------------------------------------------------------
> msuryexcel's Profile:
> http://www.excelforum.com/member.php...o&userid=28161
> View this thread: http://www.excelforum.com/showthread...hreadid=483175
>

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
Win XP & Office 2003

<snip>

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

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

--
Dana DeLouis
Win XP & Office 2003

<snip>