Which Functions Program Solver with Excel VBA

After solving a transportation problem manually with the Solver tool, let us consider examples that demonstrate its programmatic use. For solving optimization problems in VBA, the following functions are available:

• SolverAdd()
• SolverOk()
• SolverChange()
• SolverOptions()
• SolverDelete()
• SolverReset()
• SolverFinish()
• SolverSave()
• SolverLoad()
• SolverSolve()

SolverOk()

Defines the optimization problem. It sets the parameters that are normally specified manually in the Solver Parameters dialog box.

Syntax:

SolverOk(SetCell, MaxMinVal, ValueOf, ByChange)

• SetCell — specifies the target cell containing the objective function.
• MaxMinVal — specifies the type of optimization:
  • 1 = maximize
  • 2 = minimize
  • 3 = set to a specific value
• ValueOf — if MaxMinVal = 3, this parameter specifies the target value.
• ByChange — specifies the range of variable cells.

SolverAdd()

Adds a constraint to the model. Equivalent to manually adding a constraint in the Add Constraint dialog.

Syntax:

SolverAdd(CellRef, Relation, FormulaText)

• CellRef — left-hand side of the constraint (cell or range).
• Relation — integer from 1 to 5 specifying the relation type:
  • 1 = ≤
  • 2 = =
  • 3 = ≥
  • 4 = integer constraint
  • 5 = binary (0 or 1) constraint
• FormulaText — right-hand side of the constraint (cell, range, or value). For relations 4 and 5, this parameter is omitted.

SolverDelete() and SolverChange()

• SolverDelete() removes a constraint.
• SolverChange() modifies a constraint.
  Their syntax is the same as SolverAdd().

SolverDelete(CellRef, Relation, FormulaText)
SolverChange(CellRef, Relation, FormulaText)

SolverOptions()

Sets advanced options of Solver (as in the Solver Options dialog box).

Syntax:

SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, _
              Estimates, Derivatives, Search, IntTolerance, Scaling, _
              Convergence, AssumeNonNeg)

Closely related is SolverReset(), which restores default option values. It has no parameters.

SolverSolve()

Runs the Solver — equivalent to pressing the Solve button in the Solver Parameters dialog.

Syntax:

SolverSolve(UserFinish, ShowRef)

• UserFinish — Boolean. If False (default), results are displayed in the Solver Results dialog box. If True, results are applied automatically without showing the dialog.
• ShowRef — reference to a macro that is run between iterations.

Returns 0 if a solution is found.

SolverSave() and SolverLoad()

• SolverSave(SaveArea) — saves the model into the specified cell range.
  Example:
• SolverSave « Sheet1!A1:A3 »
• SolverLoad(LoadArea) — loads a model from the specified range.

SolverFinish()

Applies the results (as if pressing OK in the Solver Results dialog).

Important Note

Before using these functions in VBA:

• Set a reference to Solver in the Visual Basic Editor: Tools → References → Solver.
• If Solver is not listed in Available References, click Browse and open Solver.xlam, usually located at:

C:\Program Files\Microsoft Office\OfficeXX\Library\Solver

(where X is your Office version, e.g., Office14).