Skip to main content

SolverOk Function

Table of contents
  1. Syntax
  2. Example

Defines a basic Solver model. Equivalent to clicking Solver in the Data | Analysis group and then specifying options in the Solver Parameters dialog box.

Syntax

SolverOk SetCellMaxMinValValueOfByChangeEngineEngineDesc)

SetCell Optional Variant. Refers to a single cell on the active worksheet. Corresponds to the Set Target Cell box in the Solver Parameters dialog box. MaxMinVal Optional Variant. Corresponds to the MaxMin, and Value options in the Solver Parameters dialog box.

MaxMinVal Specifies
1 Maximize
2 Minimize
3 Match a specific value

ValueOf Optional Variant. If MaxMinVal is 3, you must specify the value to which the target cell is matched.

ByChange Optional Variant. The cell or range of cells that will be changed so that you'll obtain the desired result in the target cell. Corresponds to the By Changing Cells box in the Solver Parameters dialog box.

Engine Optional Variant. The Solving method that should be used to solve the problem: 2 for the Simplex LP method, 1 for the GRG Nonlinear method, or 3 for the Evolutionary method. Corresponds to the Select a Solving Method dropdown list in the Solver Parameters dialog box.

EngineDesc Optional Variant. An alternate way to specify the Solving method that should be used to solve the problem as a string: "Simplex LP", "GRG Nonlinear", or "Evolutionary". Corresponds to the Select a Solving Method dropdown list in the Solver Parameters dialog box.

Example

The SolverOK function defines a problem by specifying the SetCellMaxMinVal, and ByChange arguments.

Sub SolverDemo()
    Worksheets("Sheet1").Activate
    Range("F4").Formula = "=SUM(C4:E4)"
    Range("F5").Formula = "=SUM(C5:E5)"
    Range("F6").Formula = "=SUM(C6:E6)"
    Range("F7").Formula = "=SUM(F4:F6)"
    SolverReset
    SolverOptions precision:=0.001
    SolverOK SetCell:=Range("F7"), MaxMinVal:=3, ValueOf:=50, ByChange:=Range("C4:E6")
    SolverAdd CellRef:=Range("F4:F6"), Relation:=1, FormulaText:=100
    SolverAdd CellRef:=Range("C4:E6"), Relation:=3, FormulaText:=0
    SolverAdd CellRef:=Range("C4:E6"), Relation:=4
    SolverSolve UserFinish:=False
    SolverSave SaveArea:=Range("A33")
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>