Reset Model
SolverReset(): resets model and restores all the Solver settings to their defaults.
Set decision variables and objective function
SolverOK (SetCell, MaxMinVal, ValueOf, ByChange)
- SetCell as Range: the objective function cell
- MaxMinVal: maximize = 1, minimize = 2, target a specific value = 3
- ValueOf: (only if MaxMinVal = 3) the target value
- ByChange as Variant: range of decision variables
Set constraints
SolverAdd (CellRef, Relation, FormulaText)
- CellRef as Variant: range of cells that forms the left side of a constraint
- Relation: the relationship between the left and right sides of the constraint:
1 <=
2 =
3 >=
4 CellRef must have integer values
5 CellRef must have binary values
6 CellRef must have different and integer values
- FormulaText as Variant. The right side of the constraint. If you choose 4, 5, or 6, FormulaText should not be specified and CellRef must refer to decision variable cells.
Set solving options
SolverOptions (…)
MaxTime: maximum time (in seconds) to solve problem
AssumeNonNeg: ‘true’ to assume non negative decision variable (for the ones that do not have explicit lower limits)
AssumeLinear: ‘true’ to assume that the underlying model is linear
Solve the model
RunOpenSolver (SolveRelaxation As Boolean)
SolveRelaxation = ‘true’ forces OpenSolver to solve the LP relaxation of an IP
You can also use Excel Solver, but you will have a limit of 200 decision variables.
SolverSolve UserFinish:=True
The command returns the type of result the Solver found
- 0, 1, or 2 = a successful run in which a solution has been found
- 4 = there was no convergence
- 5 = no feasible solution could be found
Note: before you use these commands you need to have Solver and OpenSolver add-ins installed, enabled and referenced in the project library.