Search code examples
vbaexcelexcel-2010solverexcel-2013

Setting the convergence value to a variable for the built-in solver

In creating a program that uses the solver, I need the user to be able to manually input a value for the convergence and the precision that the built-in solver uses. I'd love to be able to set the convergence value to a cell or variable that can then be set via user input.

Here is an example of the solver code I'm using:

SolverReset
SolverOk SetCell:="$D$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$F$2:$F$5", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$F$2", Relation:=1, FormulaText:="100"
SolverAdd CellRef:="$F$2", Relation:=3, FormulaText:="0.0000000001"
SolverAdd CellRef:="$F$3", Relation:=1, FormulaText:="100"
SolverAdd CellRef:="$F$3", Relation:=3, FormulaText:="-100"
SolverAdd CellRef:="$F$4", Relation:=1, FormulaText:="100"
SolverAdd CellRef:="$F$4", Relation:=3, FormulaText:="-100"
SolverAdd CellRef:="$F$5", Relation:=1, FormulaText:="10"
SolverAdd CellRef:="$F$5", Relation:=3, FormulaText:="0.0000000001"
SolverOptions MaxTime:=0, Iterations:=0, Precision:=1E-18, Convergence:= _
    1E-16, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:= _
    1
SolverSolve True

This code is simply varying the values in cells F2, F3, F4, and F5, (within the bounds I am providing) in an attempt to minimize the value I have in D14.

Ideally I'd love to be able to put something like Convergence CellRef:="$B$16" in, but that doesn't seem to work. Does anybody know how to do this? It seems like it should be simple, but I'm coming up with a blank.

Solution

  • Convergence, and also all other SolverOptions can be set using cell values, for example Convergence:= Range("A1").value.

    Value2 is a little faster than Value, and it is good practice to explicitly cast to double the cell value (and also trap any errors).

    Another way is to add the values as named ranges or as names. This question elaborates more on these methods. Names have the advantage that do not need to appear to a spreadsheet directly, which is elegant. For example, the user can set them and edit them from a userform.

    I hope this helps!