OpenSolver API Reference

Reference documentation for OpenSolver 2.9.0 (2018.02.21)

List of all API methods

Solving

Model Construction

Model Construction (advanced – use Set methods with care)

Solver Options

Sensitivity Analysis

QuickSolve

Advanced – Methods using RefersTo strings instead of ranges

OpenSolverResult

‘ OpenSolver results, as given by OpenSolver.SolveStatus
‘ See also OpenSolver.SolveStatusString, which gives a slightly more detailed text summary
‘ and OpenSolver.SolveStatusComment, for any detailed comment on an infeasible problem
Enum OpenSolverResult
Pending = -4 ‘ Used for solvers that asynchronously and are yet to run
AbortedThruUserAction = -3 ‘ Used to indicate that a non-linearity check was made (losing the solution)
ErrorOccurred = -2 ‘ Indicate an error occured and has been reported to the user
Unsolved = -1 ‘ Indicates a model not yet solved
Optimal = 0
Unbounded = 4 ‘ objective can be made infinitely good
Infeasible = 5 ‘ There is no solution that satisifies all the constraints
LimitedSubOptimal = 10 ‘ CBC stopped before finding an optimal/feasible/integer solution because of CBC errors or time/iteration limits
NotLinear = 7 ‘ Report non-linearity so that it can be picked up in silent mode
End Enum


RunOpenSolver

Solves the OpenSolver model on the current sheet.

Public Function RunOpenSolver(Optional SolveRelaxation As Boolean = False, Optional MinimiseUserInteraction As Boolean = False, Optional LinearityOffset As Double = 10.423, Optional sheet As Worksheet) As OpenSolverResult

Name Type Description
SolveRelaxation Boolean If True, all integer and boolean constraints will be relaxed to allow continuous values for these variables. Defaults to False
MinimiseUserInteraction Boolean If True, all dialogs and messages will be suppressed. Use this when automating a lot of solves so that there are no interruptions. Defaults to False
LinearityOffset Double
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetAvailableSolvers

Gets a list of short names for all solvers that can be set

Public Function GetAvailableSolvers() As String()

(back to top)


GetChosenSolver

Gets the short name of the currently selected solver for an OpenSolver model.

Public Function GetChosenSolver(Optional sheet As Worksheet) As String

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetChosenSolver

Sets the solver for an OpenSolver model.

Public Sub SetChosenSolver(SolverShortName As String, Optional sheet As Worksheet)

Name Type Description
SolverShortName String The short name of the solver to be set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetObjectiveFunctionCell

Returns the objective cell in an OpenSolver model.

Public Function GetObjectiveFunctionCell(Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean If True, throws an error if the model is invalid. Defaults to True
RefersTo String Returns the RefersTo string describing the objective

(back to top)


SetObjectiveFunctionCell

Sets the objective cell in an OpenSolver model.

Public Sub SetObjectiveFunctionCell(ObjectiveFunctionCell As Range, Optional sheet As Worksheet)

Name Type Description
ObjectiveFunctionCell Range The cell to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetObjectiveSense

Returns the objective sense type for an OpenSolver model. Defaults to Minimize if an invalid value is saved.

Public Function GetObjectiveSense(Optional sheet As Worksheet) As ObjectiveSenseType

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetObjectiveSense

Sets the objective sense for an OpenSolver model.

Public Sub SetObjectiveSense(ObjectiveSense As ObjectiveSenseType, Optional sheet As Worksheet)

Name Type Description
ObjectiveSense ObjectiveSenseType The objective sense to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetObjectiveTargetValue

Returns the target objective value in an OpenSolver model.

Public Function GetObjectiveTargetValue(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetObjectiveTargetValue

Sets the target objective value in an OpenSolver model.

Public Sub SetObjectiveTargetValue(ObjectiveTargetValue As Double, Optional sheet As Worksheet)

Name Type Description
ObjectiveTargetValue Double The target value to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetDecisionVariables

Gets the adjustable cells for an OpenSolver model, throwing an error if unset/invalid.

Public Function GetDecisionVariables(Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean If True, throws an error if the decision variables specified are missing or invalid. Defaults to True
RefersTo String Returns the RefersTo string describing the decision variables

(back to top)


GetDecisionVariablesWithDefault

Gets the adjustable cells range (returning Nothing if invalid) for an OpenSolver model.

Public Function GetDecisionVariablesWithDefault(Optional sheet As Worksheet, Optional DecisionVariablesRefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
DecisionVariablesRefersTo String Returns the RefersTo string describing the decision variables

(back to top)


GetDecisionVariablesNoOverlap

Gets the adjustable cells range (with overlap removed) for an OpenSolver model.

Public Function GetDecisionVariablesNoOverlap(Optional sheet As Worksheet, Optional DecisionVariablesRefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
DecisionVariablesRefersTo String Returns the RefersTo string describing the decision variables

(back to top)


SetDecisionVariables

Sets the adjustable cells range for an OpenSolver model.

Public Sub SetDecisionVariables(DecisionVariables As Range, Optional sheet As Worksheet)

Name Type Description
DecisionVariables Range The range to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


AddConstraint

Adds a constraint in an OpenSolver model.

Public Sub AddConstraint(LHSRange As Range, Relation As RelationConsts, Optional RHSRange As Range, Optional RHSFormula As String, Optional sheet As Worksheet)

Name Type Description
LHSRange Range The range to set as the constraint LHS
Relation RelationConsts The relation to set for the constraint. If Int/Bin, neither RHSRange nor RHSFormula should be set.
RHSRange Range Set if the constraint RHS is a cell/range
RHSFormula String Set if the constraint RHS is a string formula
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

Note: if calling this sub using Application.Run, calling e.g.:

Application.Run "OpenSolver.xlam!AddConstraint", LHSRange, rel, RHSRange

will produce an error for reasons explained here. Instead, call:

Application.Run "OpenSolver.xlam!OpenSolverAPI.AddConstraint", LHSRange, rel, RHSRange

(back to top)


UpdateConstraint

Updates an existing constraint in an OpenSolver model.

Public Sub UpdateConstraint(Index As Long, LHSRange As Range, Relation As RelationConsts, Optional RHSRange As Range, Optional RHSFormula As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to delete
LHSRange Range The new range to set as the constraint LHS
Relation RelationConsts The new relation to set for the constraint. If Int/Bin, neither RHSRange nor RHSFormula should be set.
RHSRange Range Set if the new constraint RHS is a cell/range
RHSFormula String Set if the new constraint RHS is a string formula
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


DeleteConstraint

Deletes a constraint in an OpenSolver model.

Public Sub DeleteConstraint(Index As Long, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to delete
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


ResetModel

Clears an entire OpenSolver model.

Public Sub ResetModel(Optional sheet As Worksheet)

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


RunImportLP

Imports an LP file on to a worksheet.

Public Function RunImportLP(FilePath As String, ByRef sheet As Worksheet, Optional MinimiseUserInteraction As Boolean = False) As Variant

Example usage:

Dim ws As Worksheet
Output = OpenSolver.RunImportLP("PATHTOFILE", ws, True)
If Output = True Then
    ' Prints generated worksheet name. If the worksheet object was already allocated,
      the name is not changed
    MsgBox(ws.Name)
Else
    ' Prints error message
    MsgBox(Output)
End If
Name Type Description
FilePath String The full path to the LP file to import
sheet Worksheet The worksheet to write the model to. If the sheet does not exist, a new worksheet will be created named after the LP file name
MinimiseUserInteraction Boolean If True, all dialogs and messages will be suppressed. Use this when automating a lot of solves so that there are no interruptions. Defaults to False

(back to top)


GetNumConstraints

Returns the number of constraints in an OpenSolver model.

Public Function GetNumConstraints(Optional sheet As Worksheet) As Long

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetNumConstraints

Sets the number of constraints in an OpenSolver model. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint.

Public Sub SetNumConstraints(NumConstraints As Long, Optional sheet As Worksheet)

Name Type Description
NumConstraints Long The number of constraints to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintLhs

Returns the LHS range for a specified constraint in an OpenSolver model.

Public Function GetConstraintLhs(Index As Long, Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
Index Long The index of the constraint to modify
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean Whether to validate the LHS range. Defaults to True
RefersTo String Returns RefersTo string representation of the LHS range

(back to top)


SetConstraintLhs

Sets the constraint LHS for a specified constraint in an OpenSolver model. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint.

Public Sub SetConstraintLhs(Index As Long, ConstraintLhs As Range, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to modify
ConstraintLhs Range The cell range to set as the constraint LHS
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintRel

Returns the relation for a specified constraint in an OpenSolver model.

Public Function GetConstraintRel(Index As Long, Optional sheet As Worksheet) As RelationConsts

Name Type Description
Index Long The index of the constraint
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetConstraintRel

Sets the constraint relation for a specified constraint in an OpenSolver model. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint.

Public Sub SetConstraintRel(Index As Long, ConstraintRel As RelationConsts, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to modify
ConstraintRel RelationConsts The constraint relation to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintRhs

Returns the RHS for a specified constraint in an OpenSolver model. The Formula or value parameters will be set if the RHS is not a range (in this case the function returns Nothing).

Public Function GetConstraintRhs(Index As Long, Formula As String, value As Double, RefersToFormula As Boolean, Optional sheet As Worksheet, Optional Validate As Boolean = True) As Range

Name Type Description
Index Long The index of the constraint to modify
Formula String Returns the value of the RHS if it is a string formula
value Double Returns the value of the RHS if it is a constant value
RefersToFormula Boolean Set to true if the RHS is a string formula
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean Whether to validate the RHS range. Defaults to True

(back to top)


SetConstraintRhs

Sets the constraint RHS for a specified constraint in an OpenSolver model. Only one of ConstraintRhsRange and ConstraintRhsFormula should be set, depending on whether the RHS is a range or a string formula. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint.

Public Sub SetConstraintRhs(Index As Long, ConstraintRhsRange As Range, ConstraintRhsFormula As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to modify
ConstraintRhsRange Range Set if the constraint RHS is a cell range
ConstraintRhsFormula String Set if the constraint RHS is a string formula
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetNonNegativity

Returns whether unconstrained variables are non-negative for an OpenSolver model.

Public Function GetNonNegativity(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetNonNegativity

Sets whether unconstrained variables are non-negative for an OpenSolver model.

Public Sub SetNonNegativity(NonNegativity As Boolean, Optional sheet As Worksheet)

Name Type Description
NonNegativity Boolean True if unconstrained variables should be non-negative
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetLinearityCheck

Returns whether a post-solve linearity check will be run for an OpenSolver model

Public Function GetLinearityCheck(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetLinearityCheck

Sets the whether to run a post-solve linearity check for an OpenSolver model.

Public Sub SetLinearityCheck(LinearityCheck As Boolean, Optional sheet As Worksheet)

Name Type Description
LinearityCheck Boolean True to run linearity check
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetShowSolverProgress

Returns whether to show solve progress for an OpenSolver model.

Public Function GetShowSolverProgress(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetShowSolverProgress

Sets whether to show solve progress for an OpenSolver model.

Public Sub SetShowSolverProgress(ShowSolverProgress As Boolean, Optional sheet As Worksheet)

Name Type Description
ShowSolverProgress Boolean True to show progress while solving
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetMaxTime

Returns the max solve time for an OpenSolver model.

Public Function GetMaxTime(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetMaxTime

Sets the max solve time for an OpenSolver model.

Public Sub SetMaxTime(Optional MaxTime As Double = MAX_LONG, Optional sheet As Worksheet)

Name Type Description
MaxTime Double The max solve time in seconds (defaults to no limit)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetTolerance

Returns solver tolerance (as a double) for an OpenSolver model.

Public Function GetTolerance(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetToleranceAsPercentage

Returns solver tolerance (as a percentage) for an OpenSolver model.

Public Function GetToleranceAsPercentage(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetTolerance

Sets solver tolerance for an OpenSolver model.

Public Sub SetTolerance(Tolerance As Double, Optional sheet As Worksheet)

Name Type Description
Tolerance Double The tolerance to set (between 0 and 1)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetToleranceAsPercentage

Sets the solver tolerance (as a percentage) for an OpenSolver model.

Public Sub SetToleranceAsPercentage(Tolerance As Double, Optional sheet As Worksheet)

Name Type Description
Tolerance Double The tolerance to set as a percentage (between 0 and 100)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetMaxIterations

Returns the solver iteration limit for an OpenSolver model.

Public Function GetMaxIterations(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetMaxIterations

Sets the solver iteration limit for an OpenSolver model.

Public Sub SetMaxIterations(Optional MaxIterations As Double = MAX_LONG, Optional sheet As Worksheet)

Name Type Description
MaxIterations Double The iteration limit to set (defaults to no limit)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetPrecision

Returns the solver precision for an OpenSolver model.

Public Function GetPrecision(Optional sheet As Worksheet) As Double

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetPrecision

Sets the solver precision for an OpenSolver model.

Public Sub SetPrecision(Precision As Double, Optional sheet As Worksheet)

Name Type Description
Precision Double The solver precision to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetSolverParameters

Returns ‘Extra Solver Parameters’ range for specified solver in an OpenSolver model.

Public Function GetSolverParameters(SolverShortName As String, Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
SolverShortName String The short name of the solver for which parameters are being set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean Whether to validate the parameters range. Defaults to True
RefersTo String Returns RefersTo string representation of the parameters range

(back to top)


SetSolverParameters

Sets ‘Extra Parameters’ range for a specified solver in an OpenSolver model.

Public Sub SetSolverParameters(SolverShortName As String, SolverParameters As Range, Optional sheet As Worksheet)

Name Type Description
SolverShortName String The short name of the solver for which parameters are deleted
SolverParameters Range The range containing the parameters (must be a range with two columns: keys and parameters)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


DeleteSolverParameters

Deletes ‘Extra Parameters’ range for a specified solver in an OpenSolver model.

Public Sub DeleteSolverParameters(SolverShortName As String, Optional sheet As Worksheet)

Name Type Description
SolverShortName String The short name of the solver for which parameters are deleted
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetIgnoreIntegerConstraints

Returns whether Solver’s ‘ignore integer constraints’ option is set for an OpenSolver model. OpenSolver cannot solve while this option is enabled.

Public Function GetIgnoreIntegerConstraints(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetIgnoreIntegerConstraints

Sets Solver’s ‘ignore integer constraints’ option for an OpenSolver model. OpenSolver cannot solve while this option is enabled.

Public Sub SetIgnoreIntegerConstraints(IgnoreIntegerConstraints As Boolean, Optional sheet As Worksheet)

Name Type Description
IgnoreIntegerConstraints Boolean True to turn on ‘ignore integer constraints’
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetDuals

Returns target range for sensitivity analysis output for an OpenSolver model.

Public Function GetDuals(Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean If True, checks the Duals range for validity. Defaults to True
RefersTo String Returns the RefersTo string describing the Duals range

(back to top)


SetDuals

Sets target range for sensitivity analysis output for an OpenSolver model.

Public Sub SetDuals(Duals As Range, Optional sheet As Worksheet)

Name Type Description
Duals Range The target range for output (Nothing for no sensitivity analysis)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetDualsOnSheet

Returns whether ‘Output sensitivity analysis’ is set for an OpenSolver model.

Public Function GetDualsOnSheet(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetDualsOnSheet

Sets the value of ‘Output sensitivity analysis’ for an OpenSolver model.

Public Sub SetDualsOnSheet(DualsOnSheet As Boolean, Optional sheet As Worksheet)

Name Type Description
DualsOnSheet Boolean True to set ‘Output sensitivity analysis’
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetUpdateSensitivity

Returns True if ‘Output sensitivity analysis’ destination is set to ‘updating any previous sheet’ for an OpenSolver model, and False if set to ‘on a new sheet’.

Public Function GetUpdateSensitivity(Optional sheet As Worksheet) As Boolean

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetUpdateSensitivity

Sets the destination option for ‘Output sensitivity analysis’ for an OpenSolver model.

Public Sub SetUpdateSensitivity(UpdateSensitivity As Boolean, Optional sheet As Worksheet)

Name Type Description
UpdateSensitivity Boolean True to set ‘updating any previous sheet’. False to set ‘on a new sheet’
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetQuickSolveParameters

Gets the QuickSolve parameter range for an OpenSolver model.

Public Function GetQuickSolveParameters(Optional sheet As Worksheet, Optional Validate As Boolean = True, Optional RefersTo As String) As Range

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)
Validate Boolean If True, an error will be thrown if no range is set
RefersTo String Returns RefersTo string representation of the parameters range

(back to top)


SetQuickSolveParameters

Sets the QuickSolve parameter range for an OpenSolver model.

Public Sub SetQuickSolveParameters(QuickSolveParameters As Range, Optional sheet As Worksheet)

Name Type Description
QuickSolveParameters Range The parameter range to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


InitializeQuickSolve

Initializes QuickSolve procedure for an OpenSolver model.

Public Sub InitializeQuickSolve(Optional SolveRelaxation As Boolean = False, Optional MinimiseUserInteraction As Boolean = False, Optional LinearityCheckOffset As Double = 0, Optional sheet As Worksheet)

Name Type Description
SolveRelaxation Boolean If True, all integer and boolean constraints will be relaxed to allow continuous values for these variables. Defaults to False
MinimiseUserInteraction Boolean If True, all dialogs and messages will be suppressed. Use this when automating a lot of solves so that there are no interruptions. Defaults to False
LinearityCheckOffset Double Sets the base value used for checking if the model is linear. Change this if a non-linear model is not being detected as non-linear. Defaults to 0
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


RunQuickSolve

Runs a QuickSolve for currently initialized QuickSolve model.

Public Function RunQuickSolve(Optional SolveRelaxation As Boolean = False, Optional MinimiseUserInteraction As Boolean = False) As OpenSolverResult

Name Type Description
SolveRelaxation Boolean
MinimiseUserInteraction Boolean If True, all dialogs and messages will be suppressed. Use this when automating a lot of solves so that there are no interruptions. Defaults to False

(back to top)


ClearQuickSolve

Clears any initialized QuickSolve.

Public Sub ClearQuickSolve()

(back to top)


GetObjectiveFunctionCellRefersTo

Returns the RefersTo string for the objective in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetObjectiveFunctionCellRefersTo(Optional sheet As Worksheet) As String

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetObjectiveFunctionCellRefersTo

Sets the objective cell using a RefersTo string in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetObjectiveFunctionCellRefersTo(ObjectiveFunctionCellRefersTo As String, Optional sheet As Worksheet)

Name Type Description
ObjectiveFunctionCellRefersTo String
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetDecisionVariablesRefersTo

Returns the RefersTo string for the decision variables in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetDecisionVariablesRefersTo(Optional sheet As Worksheet) As String

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetDecisionVariablesRefersTo

Sets the adjustable cells using a RefersTo string for an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetDecisionVariablesRefersTo(DecisionVariablesRefersTo As String, Optional sheet As Worksheet)

Name Type Description
DecisionVariablesRefersTo String The RefersTo string describing the decision variable range to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


UpdateConstraintRefersTo

Updates an existing constraint in an OpenSolver model using RefersTo strings. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub UpdateConstraintRefersTo(Index As Long, LHSRefersTo As String, Relation As RelationConsts, RHSRefersTo As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to update
LHSRefersTo String The new RefersTo string to set as the constraint LHS
Relation RelationConsts The new relation to set for the constraint. If Int/Bin, neither RHSRange nor RHSFormula should be set.
RHSRefersTo String The new RefersTo string to set as the constraint RHS
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintRefersTo

Gets the constraint description in RefersTo format for the specified constraint in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub GetConstraintRefersTo(Index As Long, LHSRefersTo As String, Relation As RelationConsts, RHSRefersTo As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint
LHSRefersTo String Returns the RefersTo string describing the constraint LHS
Relation RelationConsts Returns the constraint relation type
RHSRefersTo String Returns the RefersTo string describing the constraint RHS
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintLhsRefersTo

Returns the RefersTo string for the LHS of the specified constraint in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetConstraintLhsRefersTo(Index As Long, Optional sheet As Worksheet) As String

Name Type Description
Index Long The index of the constraint
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetConstraintLhsRefersTo

Sets the constraint LHS using a RefersTo string for a specified constraint in an OpenSolver model. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetConstraintLhsRefersTo(Index As Long, ConstraintLhsRefersTo As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to modify
ConstraintLhsRefersTo String The RefersTo string to set as the constraint LHS
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetConstraintRhsRefersTo

Returns the RefersTo string for the LHS of the specified constraint in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetConstraintRhsRefersTo(Index As Long, Optional sheet As Worksheet) As String

Name Type Description
Index Long The index of the constraint
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetConstraintRhsRefersTo

Sets the constraint RHS using a RefersTo string for a specified constraint in an OpenSolver model. Using Set methods to modify constraints is dangerous, it is best to use Add/Delete/UpdateConstraint. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetConstraintRhsRefersTo(Index As Long, ConstraintRhsRefersTo As String, Optional sheet As Worksheet)

Name Type Description
Index Long The index of the constraint to modify
ConstraintRhsRefersTo String The RefersTo string to set as the constraint RHS
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetSolverParametersRefersTo

Returns the RefersTo string for the ‘Extra Solver Parameters’ range for specified solver in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetSolverParametersRefersTo(SolverShortName As String, Optional sheet As Worksheet) As String

Name Type Description
SolverShortName String The short name of the solver for which parameters are being returned
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetSolverParametersRefersTo

Sets the ‘Extra Parameters’ range using a RefersTo string for a specified solver in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetSolverParametersRefersTo(SolverShortName As String, SolverParametersRefersTo As String, Optional sheet As Worksheet)

Name Type Description
SolverShortName String
SolverParametersRefersTo String The RefersTo string to set as the extra parameters range
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetDualsRefersTo

Returns the RefersTo string for the sensitivity analysis output in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetDualsRefersTo(Optional sheet As Worksheet) As String

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetDualsRefersTo

Sets target range for sensitivity analysis output using a RefersTo string for an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetDualsRefersTo(DualsRefersTo As String, Optional sheet As Worksheet)

Name Type Description
DualsRefersTo String The RefersTo string describing the target range for output (Nothing for no sensitivity analysis)
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


GetQuickSolveParametersRefersTo

Returns the RefersTo string for the QuickSolve parameter range in an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Function GetQuickSolveParametersRefersTo(Optional sheet As Worksheet) As String

Name Type Description
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)


SetQuickSolveParametersRefersTo

Sets the QuickSolve parameter range using a RefersTo string for an OpenSolver model. WARNING: Do not use RefersTo methods unless you know what you are doing!

Public Sub SetQuickSolveParametersRefersTo(QuickSolveParametersRefersTo As String, Optional sheet As Worksheet)

Name Type Description
QuickSolveParametersRefersTo String The RefersTo string describing the parameter range to set
sheet Worksheet The worksheet containing the model (defaults to active worksheet)

(back to top)

65 thoughts on “OpenSolver API Reference”

  1. Hi, I want to report a bug.
    OpenSolver gives errors when I have special characters such as | in the sheet name. When I have this character in the sheet name and I click the Model button, it gives error. If I add special character once after I managed to open Model window, it doesn’t cause an error. However, this error always occurs when I try to run OpenSolver by VBA code.

  2. I just downloaded OpenSolver from SourceForge and loaded it into my EXCEL Add-Ins directory. I am running Office 2010. After clicking on “OpenSolver.XLAM”, OpenSolver shows up when I click on the “Data” tab in EXCEL. However when I click on Model (for example), I get the error message, “The specified solver (CBC) is not in the list of available solvers. Please see OpenSolver API module for the list of available solvers.” Wondering how to get OpenSolver to work on my system (running Windows 7)?

    1. Sounds like you have copied just the .xlam file and not the other folders and files. Furthermore there is no need to install OpenSolver…. just open it from anywhere, at least for your first test. Andrew

      1. Hi, thanks for your quick response!
        – following are the files that I copied to the EXCEL add-in folder:
        cbc.exe
        CHANGELOG.md
        COIN LICENSE.txt
        gurobiOSRun.py
        LICENSE.txt
        OpenSolver.xlam
        README.md
        By the way, when I click on OpenSolver.xlam it ends up under the data tab; however I get the error message, “The specified solver (CBC) is not in the list of available solvers. Please see OpenSolver API module for the list of available solvers.” Also, OpenSolver.xlam does not show up in EXCEL’s aAdd-In list?

        1. Did you move them out of their folders? That’s a bad thing to do. Please quit excel. Unzip opensolver
          And all its files/folders into say c:/temp and open OpenSolver.xlam. Andrew

        2. Hi, thanks for the quick reply – I did as you suggested: downloaded fresh ZIP file from SourceForge, moved it to C:temp folder, unzipped into folder (I notice that in the zip file there are two instances of “cbc.exe”, so could not load them both into the temp file; random one loaded)

          results remain the same: when I click on “Model” (for example, OpenSolver returns the error message, ““The specified solver (CBC) is not in the list of available solvers. Please see OpenSolver API module for the list of available solvers”?

          1. Don’t move any files or folders… Unzip the whole opensolver folder into c:\temp! Please try again. Andrew

          2. Hi,

            This time I downloaded the Zip file directly to C:temp folder and extracted all files to the same folder. Result is the same, when I click on ‘Model”, I can see a tantalizing Model form waiting to be filled out; however it is partially blocked by the Microsoft Visual Basic”Run-Time error ‘-2147220503 (800403e9)’: “The specified solver (CBC) is not in the list of available solvers. Please see OpenSolver API module for the list of available solvers” When I press the “Debug” button on the Visual Basic Error window, I just get an hourglass that goes on forever. After a bit I get the “Microsoft Excel has stopped working” message – the program has crashed with the below helpful information?
            Problem signature:
            Problem Event Name: APPCRASH
            Application Name: EXCEL.EXE
            Application Version: 14.0.7188.5000
            Application Timestamp: 59a06508
            Fault Module Name: VBE7.DLL
            Fault Module Version: 7.0.16.43
            Fault Module Timestamp: 58cae920
            Exception Code: c0000005
            Exception Offset: 001f9564
            OS Version: 6.1.7601.2.1.0.256.48
            Locale ID: 1033

            Additional information about the problem:
            LCID: 1033
            skulcid: 1033

            Read our privacy statement online:
            http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

            If the online privacy statement is not available, please read our privacy statement offline:
            C:\Windows\system32\en-US\erofflps.txt

            1. Just extract the zipped folders and files without moving any files at all. It is packaged ready to run with the files in the right folders. Andrew

    2. Nice to meet you.
      I have seen the same error message.
      When the Excel sheet name was “Ax = b”, an error occurred, and if the name was changed to a name that did not include “=”, it could be executed without any problems.
      ——-
      Windows 10, Excel 2016

  3. Hi,

    Could you please let me know the API reference function for setting a particular solver engine in VBA for Open Solver?

    Thanks.

  4. Hello OpenSolver Community,
    I’m currently working on a project using OpenSolver & VBA, my aim is to program the construction & the solving phases of the model. I have some difficulties to write the code of solving the model, I need your help concerning this issue.

    I’ve wrote the code of model construction and it works, thank you to provide the solving phase code.

    Regards

  5. While trying to add a multiplication constraint for a programmatically initialized opensolver, I get stuck on the syntax for a RHS of a constraint that is a multiplication of two cells:

    I have tried:

    Dim string1 As String
    string1 = “$D$29*$D$30”

    ‘OpenSolver.AddConstraint Sheet.range(“D28”), RelationLE, Sheet.range(“D28”)*Sheet.range(“D29”)
    ‘OpenSolver.AddConstraint Sheet.range(“D28”), RelationLE, (Sheet.range(“D28”)*Sheet.range(“D29”))
    ‘OpenSolver.AddConstraint Sheet.range(“D28”), RelationLE, Sheet.range(“D28*D29”)
    ‘OpenSolver.AddConstraint Sheet.range(“D28″), RelationLE, FormulaText:=”$D$29*$D$30”

    OpenSolver.AddConstraint Sheet.range(“D28″), RelationLE, string1
    ‘SolverAdd CellRef:=”$D$28″, Relation:=1, FormulaText:=”$D$29*$D$30” ‘Learned from regular excel solver recording.

    But none of the above do the trick, and recording a macro where I manually set the multiplication constraint in the opensolver model does not record anything, so I can’t learn from that syntax.

    So if someone would happen to know how to programmatically add the constraint in the RHS where the multiplication of two cells is set, I would appreciate the information 🙂

    1. The named argument is RHSFormula rather than FormulaText:

      OpenSolver.AddConstraint Sheet.range("D28"), RelationLE, RHSFormula:="$D$29*$D$30"

  6. Hello,

    I am trying to implement a dynamic optimization problem via branch and bound in OpenSolver using VBA and facing a quite simple problem, at least I hope so. I am trying to model an optimization problem for a storage system.

    I do not get to declaring constraints as “functional constraints”, which are the connections of variables within my model. For example, I want to set declare a variable/constraint that is fixed for t=0 but varies from t=1 to t=23 (Charging of storage). In the period t=0, the storage charge changes (charging status at t=0 +/- delta). This delta again changes from time to time, but again is a result of the optimisation. In case i declare the constraint, the delta (which will be written in a specific cell) is assumed as zero and the constraint gets a fixed value which is not correct.

    Therefore, I need to know how it is possible to declare “functional constraints” which are part of the optimisation problem.

    I hope I explained to problem in an understandable way. I really appreciate your help and am looking forward to your answer.

    Best regards
    Max

    1. Try looking up a time indexed optimisation model. I suggest you keep a fixed time step. Andrew

    1. Can you be more explicit; exactly what is happening? Any screenshots you can email to email hidden; JavaScript is required? Andrew

  7. Hello,

    I tried to implement the following code however I get ByRef error.

    For i = 1 To nrow_skill
    For k = 1 To nrow_job
    For j = 1 To nrow_worker
    array2(k, i) = schedule.Cells(j, k) * workVskill.Cells(j, i)
    Next j
    opensolver.AddConstraint array2(k, i), RelationEQ, compVskill.Cells(k, i)
    Next k
    Next i

    what do you think about the code? What is wrong with it?

    Thanks!

    1. It looks like the first argument to `AddConstraint` is not a `Range` which is why there is an error. If you want to use the result of `schedule.Cells(j, k) * workVskill.Cells(j, i)` as the LHS, you should make new cells on the sheet that contain the result of this product and use these cells as the LHS argument.

  8. Hi,

    Thanks for Opensolver, it works great! I need to modify constraints (relation) from VBA using UpdateConstraint, and I also need some duals. The question is where can I find the indexes of the constraints in the model? To be able to refer to the index of the constraint in question.

    Thanks

    1. You will need to use the GetConstraint___ methods to find out which constraint you want to modify. You can use GetNumConstraints to find the number of constraints, and then loop over these until you find the index that matches the constraint you are trying to modify.

  9. Hello,

    This is the first time I work with API and I keep having an ‘424 Object Required error’. I almost copied te code as explained above for Jack.

    Sub solveLpProblem()
    Dim TestSheet As Worksheet
    Set TestSheet = Worksheets(“LP Everything Known”)

    OpenSolver.ResetModel Sheet:=TestSheet

    ‘Objective Definition
    OpenSolver.SetObjectiveFunctionCell TestSheet.Range(Cells(1, 2), Cells(1, 2)), Sheet:=TestSheet
    OpenSolver.SetObjectiveSense MaximiseObjective, Sheet:=TestSheet

    ‘Variables Definition
    OpenSolver.SetDecisionVariables TestSheet.Range(Cells(5, 9), Cells(19, 11)), Sheet:=TestSheet

    ‘Constraints Definition
    OpenSolver.AddConstraint TestSheet.Range(Cells(21, 9), Cells(21, 11)), RelationGE, TestSheet.Range(Cells(20, 9), Cells(20, 11)), Sheet:=TestSheet
    OpenSolver.AddConstraint TestSheet.Range(Cells(5, 2), Cells(103, 2)), RelationEQ, TestSheet.Range(Cells(5, 61), Cells(104, 61)), Sheet:=TestSheet
    OpenSolver.AddConstraint TestSheet.Range(Cells(5, 8), Cells(103, 60)), RelationGE, “0”, Sheet:=TestSheet

    End Sub

    Do you have any idea what I am doing wrong?

    Thanks!

    1. I’m not sure what the error is referring to, but I made a couple of modifications to the code to get it to work on my machine (not including changing the sheet name). The changes were in the second constraint altering the size of the second range so that it matched the size of the first (5–103 and 5–104 are not the same length). In the third constraint, it is expecting a range argument for the the RHS of the constraint, so you need to use `RHSFormula:=”0″` to make that work. Let me know if you still have problems after than, and feel free to send the sheet through to email hidden; JavaScript is required


      Sub solveLpProblem()
      Dim TestSheet As Worksheet
      Set TestSheet = Worksheets("Sheet1")

      OpenSolver.ResetModel Sheet:=TestSheet

      'Objective Definition
      OpenSolver.SetObjectiveFunctionCell TestSheet.Range(Cells(1, 2), Cells(1, 2)), Sheet:=TestSheet
      OpenSolver.SetObjectiveSense MaximiseObjective, Sheet:=TestSheet

      'Variables Definition
      OpenSolver.SetDecisionVariables TestSheet.Range(Cells(5, 9), Cells(19, 11)), Sheet:=TestSheet

      'Constraints Definition
      OpenSolver.AddConstraint TestSheet.Range(Cells(21, 9), Cells(21, 11)), RelationGE, TestSheet.Range(Cells(20, 9), Cells(20, 11)), Sheet:=TestSheet
      OpenSolver.AddConstraint TestSheet.Range(Cells(5, 2), Cells(103, 2)), RelationEQ, TestSheet.Range(Cells(5, 61), Cells(103, 61)), Sheet:=TestSheet
      OpenSolver.AddConstraint TestSheet.Range(Cells(5, 8), Cells(103, 60)), RelationGE, RHSFormula:="0", Sheet:=TestSheet

      End Sub

  10. Hello,

    I have been constructing my optimization model and solving it using OpenSolver. It all works very well when I perform each step in the spreadsheet. However, I would like to automate model building and obtaining a solution with VBA.

    When I have a model already constructed, I use the commands below (from the website) and there is no problem.

    Dim Result As OpenSolverResult
    Result = RunOpenSolver(False, True)

    Then, I still use the two lines above and construct the model using Solver (create the model with Record Macro tool). It again works very well. However, I would like to construct the model using OpenSolver. When I attempt to do that, for example, when I just use OpenSolver.ResetModel Sheet:=”test” (like it appears on https://gist.github.com/JackDunnNZ/917ad455b290161ed59d), I receive an error for ResetModel. It says “Method or data member not found.” I have the OpenSolver open; I have the reference to OpenSolver. I must be missing additional references and I wonder if you could help me identify what they may be.

    Thank you!

    1. I’m not entirely sure what the problem is here. If you are using this


      OpenSolver.ResetModel Sheet:=”test”

      make sure that the argument to Sheet:= is a Worksheet object and not a string, e.g.


      OpenSolver.ResetModel Sheet:=Sheets("test")

      If you still have trouble send a copy of the sheet through to email hidden; JavaScript is required and I can take a look

  11. Hi! Open Solver is already referenced in my VBA project. However, not all API methods appear after “OpenSolver.”
    Can you help me with this? Thanks in advance!

    1. Does other autocomplete work in the project, or is it just `OpenSolver.` that isn’t working?

      Could you try the following:
      1. Close and reopen Excel (to clear anything potentially caused by your current project)
      2. Create a new workbook
      3. Add reference to OpenSolver
      4. See if autocomplete works

      If that doesn’t work, let me know your version info (go to `OpenSolver > About OpenSolver` and copy the version info at the top)

      1. Hi! Autocomplete works fine in the project. I need to use OpenSolver.SetObjectiveFunctionCell, OpenSolver.SetObjectiveSense, OpenSolver.SetDecisionVariables, OpenSolver.AddConstraint but those methods dont appear in the autocomplete. My Open Solver version is 2.6.2.

        By the way, will it affect the capabilities of Open Solver if the product activation failed with my MS Excel? Thanks!

        1. The API was only added in version 2.7.0, try updating to the latest version. I don’t think activation should change anything

          1. Downloaded the latest version and the autofill of API methods works perfectly fine, thank you!

            By the way, what’s the maximum number of constraints that the open solver can handle? Thanks again!

            1. Glad to hear it’s working! There are no artificial limits on problem size, the only limit is whatever your computer can handle.

  12. Thanks for the answer.

    But I still have the same “Error 424 : Object Required” that points on the line “OpenSolver.ResetModel Sheet:=TestSheet”

    Is there any Excel/VBA option that could make my function unable to call OpenSolver function ?

    (The union is an other test. I have to do it recursively, line by line, because in each line variables are not at the same place. So I wanted to see if “SetDecisionVariables” overwrite or not the precedent “SetDecisionVariables”)

      1. Jack: It seems like we can add a maximum of 33 separate decision variables using OpenSolver input box(it’s unlimited for a range of decision variables which are next to each other though). I tried your procedure and still could not get past that limit.

        I need to add 1000 separate decision variables for my binary lp model (some sort of allocation problem), do you have any advice or workaround?

        I though about making all the cells on my worksheet as the decision variable, and then force the non-decision variable cells to get a value of 0. However, it took me forever to initialize this model…

        Thanks,
        Vinny.

        1. Vinny: The limit on the string length of the range that defines the decision variables is imposed by Excel. Having 1000 different decision cells spread over your spreadsheet is not an ideal design if working with OpenSolver. (As well as being unwieldy, it also makes things run a bit slower.) You can instead put formulae in each of these 1000 cells linking back to a particular cell in a single block of 1000 decision cells that is entered as a single range in the OpenSolver dialog. Other users have found this approach has worked for them. I hope this helps. Andrew

  13. Hello,

    I’m trying to use API. I’m testing the code below :

    Sub Model_Creation()

    Sheets(“test”).Select

    OpenSolver.ResetModel Sheet:=”test”

    ‘Objective Definition
    OpenSolver.SetObjectiveFunctionCell Sheets(“test”).Cells(1, 1), Sheet:=”test”
    OpenSolver.SetObjectiveSense MinimiseObjective, Sheet:=”test”

    ‘Variables Definition
    OpenSolver.SetDecisionVariables Sheets(“test”).Range(Cells(3, 1), Cells(3, 4)), Sheet:=”test”
    OpenSolver.SetDecisionVariables Sheets(“test”).Range(Cells(5, 1), Cells(5, 4)), Sheet:=”test”

    ‘Constraints Definition
    OpenSolver.AddConstraint Sheets(“test”).Range(Cells(3, 1), Cells(3, 4)), RelationBIN, Sheet:=”test”
    OpenSolver.AddConstraint Sheets(“test”).Range(Cells(7, 1), Cells(7, 4)), RelationLE, Sheets(“test”).Range(Cells(8, 1), Cells(8, 4)), Sheet:=”test”
    OpenSolver.AddConstraint Sheets(“test”).Range(Cells(10, 1), Cells(10, 4)), RelationGE, Sheets(“test”).Range(Cells(11, 1), Cells(11, 4)), Sheet:=”test”

    End Sub

    But it alwys end up with an Error 424 Object Required !
    OpenSolver.xlam is well loaded, Module OpenSolverAPI is well loaded

    Do you have any idea why it’s going wrong ?

    Thanks

    1. Thanks for trying out the API and letting us know! The problem here is that the `Sheet` argument needs to be a Worksheet object rather than a String. I’ve fixed up the code to use a Worksheet object here: https://gist.github.com/JackDunnNZ/917ad455b290161ed59d

      There are a couple of other changes I made:

      • You don’t need to select the sheet before making changes to the model
      • You need to set all the decision variables in one call to `SetDecisionVariables`. The way to do this is to use the `Union` function of VBA

      Please let me know if there’s anything not clear or if you have any other problems.

Leave a Reply to Jack Dunn (Team OpenSolver) Cancel reply

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