+ Reply to Thread
Results 1 to 2 of 2

Return Value for for which solver gives an error

  1. #1
    Registered User
    Join Date
    12-09-2016
    Location
    Karachi
    MS-Off Ver
    2010
    Posts
    1

    Return Value for for which solver gives an error

    I have a solver code that runs in a loop 5 times.

    I know that for some values ( lets say 1,2 and 3), the solver will run fine and then give an error as the problem cannot be solved

    I want to know what is the last value for which solver gives a correct solution or the first value as which it gives an error

    In short, is it possible to return 1 when solver gives a right solution and return 0 when it does not give a solution. Thanks

    For i = 1 To 5
    SolverReset
    Range("D23").Select
    ActiveCell.FormulaR1C1 = i
    Range("D24").Select
    SolverOk SetCell:="$X$23", MaxMinVal:=1, ValueOf:=0, ByChange:="$F$23:$V$23", _
    Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$F$42:$V$42", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$W$23", Relation:=2, FormulaText:="$B$15"
    SolverOk MaxMinVal:=0, ValueOf:=0, ByChange:="$F$23:$V$33", Engine:=2, _
    EngineDesc:="Simplex LP"
    SolverOk SetCell:="$X$23", MaxMinVal:=1, ValueOf:=0, ByChange:="$F$23:$V$23", _
    Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$F$23:$V$23", Relation:=4, FormulaText:="integer"
    SolverOk SetCell:="$X$23", MaxMinVal:=1, ValueOf:=0, ByChange:="$F$23:$V$23", _
    Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$X$23", MaxMinVal:=1, ValueOf:=0, ByChange:="$F$23:$V$23", _
    Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$F$23:$V$23", Relation:=1, FormulaText:="3"
    SolverOk SetCell:="$X$23", MaxMinVal:=1, ValueOf:=0, ByChange:="$F$23:$V$23", _
    Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve (True)
    If Solver_Solved =1 Then
    j = i - 1
    End If

    Next i

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Return Value for for which solver gives an error

    I think you code could be "trimmed" a bit. You also need to wrap you code when posting.

    Please Login or Register  to view this content.
    As for capturing the value the gives an error perhaps this info could be of help:

    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.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 09-22-2015, 06:09 PM
  2. Can't start Solver. Error message says Solver.xlam already open.
    By DaveHills in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-21-2012, 11:02 AM
  3. error while using solver in VBA
    By BHudPE in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2010, 04:35 PM
  4. How to get solver to return only whole numbers?
    By Headies in forum Excel General
    Replies: 3
    Last Post: 03-31-2008, 06:23 PM
  5. Interesting Solver problem (Solver encounters an error)
    By MrShorty in forum Excel General
    Replies: 3
    Last Post: 12-22-2005, 06:55 PM
  6. Solver GUI works fine, but the VBA macro won't return any results
    By Ben Anderson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2005, 10:05 AM
  7. [SOLVED] Why will solver only return the original adjustable cell values?
    By Del in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-03-2005, 12:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1