VBA Syntax
SolverSolve(UserFinish:=, ShowRef:=)
Macro Language Syntax
=SOLVER.SOLVE(user_finish, show_ref)
UserFinish is a logical value specifying whether to show the standard Solver Results dialog box.
If UserFinish is TRUE, SolverSolve returns its integer value without displaying anything. Your VBA function or macro should decide what action to take (for example, by examining the return value or presenting its own dialog box); it must call SolverFinish in any case to return the worksheet to its proper state.
If UserFinish is FALSE or omitted, Solver displays the standard Solver Results dialog box, allowing the user to keep or discard the final solution values, and optionally produce reports.
ShowRef is a VBA function or macro to be called in place of displaying the Show Trial Solution dialog box. It is used when you want to regain control whenever Solver finds a new intermediate solution value. Here is an example of defining and using the argument ShowRef:
sub test
answer=SolverSolve(True,"ShowTrial")
end sub
function ShowTrial(Reason as integer)
msgbox Reason
ShowTrial=true
end function
The argument Reason, which must be present, is an integer value from 1 to 3:
Function called (on every iteration) because the Show Iteration Results box in the Solver Options dialog was checked, OR function called because the user pressed ESC to interrupt the Solver.
Function called because the Max Time option in the Solver Options dialog was exceeded.
Function called because the Max Iterations option in the Solver Options dialog was exceeded.
The function must return TRUE if the Solver should continue running (same as the Continue button in the Show Trial Solution dialog), or FALSE if it should stop (same as the Stop button).
For this argument to have an effect, the Show Iteration Results check box must be selected in the Solver Options dialog box. This can be done manually by selecting the check box, or automatically by calling SolverOptions in your VBA module or macro.
The VBA function or macro you call can inspect the current solution values on the worksheet or take other actions such as saving or charting the intermediate values. It must return the value TRUE if the solution process is to continue, or FALSE if the solution process should stop at this point.
Remarks
If a Solver problem has not been completely defined, SolverSolve returns the #N/A error value. Otherwise the Solver "engine" is started, and the problem specifications are passed to it. When the solution process is complete, SolverSolve returns an integer value indicating the stopping condition:
Value Stopping Condition
0 Solver found a solution. All constraints and optimality conditions
are satisfied.
1 Solver has converged to the current solution. All constraints are
satisfied.
2 Solver cannot improve the current solution. All constraints are
satisfied.
3 Stop chosen when the maximum iteration limit was reached.
4 The Set Cell values do not converge.
5 Solver could not find a feasible solution.
6 Solver stopped at user's request.
7 The conditions for Assume Linear Model are not satisfied.
8 The problem is too large for Solver to handle.
9 Solver encountered an error value in a target or constraint cell.
10 Stop chosen when maximum time limit was reached.
11 There is not enough memory available to solve the problem.
12 Another Excel instance is using SOLVER.DLL. Try again later.
13 Error in model. Please verify that all cells and constraints are
valid.
Bookmarks