Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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 -- msuryexce ----------------------------------------------------------------------- msuryexcel's Profile: http://www.excelforum.com/member.php...fo&userid=2816 View this thread: http://www.excelforum.com/showthread.php?threadid=48317 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
"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" wrote in message ... 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching max iterations Stop of Solver in VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Anyone know how to display iterations from solver? | Excel Worksheet Functions | |||
globally catching any error event? | Excel Programming | |||
Catching VbMsgBoxResult | Excel Programming | |||
Catching NewSheet-Event in another WorkBook | Excel Programming | |||
Catching an error | Excel Programming |