[VBA EXCEL] Automating the Solver Stop Command
didibonf
-
didibonf Posted messages 418 Registration date Status Member Last intervention -
didibonf Posted messages 418 Registration date Status Member Last intervention -
Hello,
I have coded in VBA to run the solver. However, my macro is interrupted by the message 'Maximum time reached. Continue?'. I would like to automate the response: Stop and keep the solver's solution while continuing my macro.
Can you help me?
I have coded in VBA to run the solver. However, my macro is interrupted by the message 'Maximum time reached. Continue?'. I would like to automate the response: Stop and keep the solver's solution while continuing my macro.
Can you help me?
4 answers
Hello,
You can try sending the correct answers to the dialog box using sendkeys().
eric
edit: if it helps, excerpt from https://support.microsoft.com/en-us/help/843304 :
The SolverSolve function
The SolverSolve function solves the model using the parameters you specified with the SolverOK function. Executing the SolverSolve function is equivalent to clicking on solve in the Solver Parameters dialog box. Here is the syntax for the SolverSolve function:
SolverSolve(UserFinish, ShowRef)
The following information describes the syntax of the SolverSolve function:
? UserFinish indicates whether you want the user to finish solving the model.
To return the results without displaying the Solver results dialog box, set this argument to TRUE. To return the results and display the Solver Results dialog box, set this argument to FALSE
? ShowRef identifies the macro that is called when the Microsoft Excel Solver returns an intermediate solution.
The ShowRef argument must only be used when the value TRUE is passed to the StepThru argument of the SolverOptions function.
The SolverFinish function
The SolverFinish function indicates what to do with the results and what type of report to create after the solving process is completed. Here is the syntax of the SolverFinish function:
SolverFinish (KeepFinal, ReportArray)
The following information describes the syntax of the SolverFinish function:
? KeepFinal indicates how to treat the final result. If KeepFinal is 1, the final solution values are kept in the variable cells, replacing the values. If KeepFinal is 2, the final solution values are ignored and the previous values are restored.
? ReportArray specifies an array that indicates the type of report Microsoft Excel creates when the solution is reached. If ReportArray is set to 1, Microsoft Excel creates a answers report. If set to 2, Microsoft Excel creates a criteria report and if set to 3, Microsoft Excel creates a limits report. For more information on these reports, see the section "How to generate reports for solutions".
You can try sending the correct answers to the dialog box using sendkeys().
eric
edit: if it helps, excerpt from https://support.microsoft.com/en-us/help/843304 :
The SolverSolve function
The SolverSolve function solves the model using the parameters you specified with the SolverOK function. Executing the SolverSolve function is equivalent to clicking on solve in the Solver Parameters dialog box. Here is the syntax for the SolverSolve function:
SolverSolve(UserFinish, ShowRef)
The following information describes the syntax of the SolverSolve function:
? UserFinish indicates whether you want the user to finish solving the model.
To return the results without displaying the Solver results dialog box, set this argument to TRUE. To return the results and display the Solver Results dialog box, set this argument to FALSE
? ShowRef identifies the macro that is called when the Microsoft Excel Solver returns an intermediate solution.
The ShowRef argument must only be used when the value TRUE is passed to the StepThru argument of the SolverOptions function.
The SolverFinish function
The SolverFinish function indicates what to do with the results and what type of report to create after the solving process is completed. Here is the syntax of the SolverFinish function:
SolverFinish (KeepFinal, ReportArray)
The following information describes the syntax of the SolverFinish function:
? KeepFinal indicates how to treat the final result. If KeepFinal is 1, the final solution values are kept in the variable cells, replacing the values. If KeepFinal is 2, the final solution values are ignored and the previous values are restored.
? ReportArray specifies an array that indicates the type of report Microsoft Excel creates when the solution is reached. If ReportArray is set to 1, Microsoft Excel creates a answers report. If set to 2, Microsoft Excel creates a criteria report and if set to 3, Microsoft Excel creates a limits report. For more information on these reports, see the section "How to generate reports for solutions".
My code is as follows:
SolverOk SetCell:="$N$204", MaxMinVal:=2, ValueOf:="0", ByChange:="$K$3:$K$202"
SolverSolve True
Where should I insert the code and what should I put to make it stop the solver instead of continuing?
SolverOk SetCell:="$N$204", MaxMinVal:=2, ValueOf:="0", ByChange:="$K$3:$K$202"
SolverSolve True
Where should I insert the code and what should I put to make it stop the solver instead of continuing?