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. Pingback: Utilização do OpenSolver com VBA | Ferramentas em Excel-Vba

  2. I have a model in which I am trying to do several iterations with changing some named range parameters. It works great, but after every iteration the problem needs to be setup again. Is there a way to just update the certain parameters and not have to reload the whole problem?
    I have used SetQuickSolveParameters to indicate the parameters that are changing, the model solves, but the results don’t make sense compared to running the model with setting up the problem again.

    This is the current order of calls:
    SetQuickSolverParameters(ActiveSheet.Range(“namedRanges”))
    RunQuickSolve()
    … parameters get updated, then:
    SetQuickSolverParameters(ActiveSheet.Range(“namedRanges”))
    RunQuickSolve()…

    I have tried several iterations with GetQuickSolveParameters too, but haven’t been successful. If you have any insights please let me know. Thanks.

    Reply

  3. Good Morning,

    I have a dynamic model that is working great! The problem that I have been running into though is that some user set constraints can produce an infeasible solution. There is no error code popup that is produced when this happens, solver will just run for a very extended amount of time.

    Is there some code that I could add to get the status or error code so that I can create a message box that will inform the user that they need to adjust some of the constraints?

    The main thing that I need is a line of code that will produce the error code (i.e. 5 – infeasible solution)

    Reply

    1. If it runs a long time, then CBC cannot tell that it is infeasible. So, sorry, but there is not shortcut for determining this. Andrew

      Reply

  4. Hi,

    Trust you are doing well.
    I am trying to use a call to SetDecisionVariables using named ranges. Is that possible? Whats the syntax to be followed?

    Thanks.
    KM

    Reply

  5. Hello, I’m having difficulties in 2 things using Opensolver in VBA (I’m a noob, i know it):
    1) I need to declare different ranges of variables, so I tried
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“D4:FJ4”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“D5:D125”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“E125:GK125”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“GK58:GK124”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“FI58:GJ58”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“FI59:FI124”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“AC86:FH86”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“AB5:AB97”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“AC28:FJ28”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“FJ5:FJ27”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“DX5:DX27”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“BX5:BX27”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“E67:AA67”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“E98:FI98”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“BJ87:BJ124”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“DI99:DI124”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“FJ95:GJ95”), sheet:=WSheet
    ‘OpenSolver.SetDecisionVariables WSheet.Range(“FH80”), sheet:=WSheet
    But in the end I have only the last (FH80) as a variable, like it is overwriting and not updating.

    Then I tried :
    OpenSolver.SetDecisionVariables WSheet.Range(“D4:FJ4”, “D5:D125”, “E125:GK125”,
    “GK58:GK124”, “FI58:GJ58”, “FI59:FI124”, “AC86:FH86”, “AB5:AB97”, “AC28:FJ28”, “FJ5:FJ27”,
    “DX5:DX27”, “BX5:BX27”, “E67:AA67”, “E98:FI98”, “BJ87:BJ124”, “DI99:DI124”, “FJ95:GJ95”,
    “FH80”), sheet:=WSheet
    But it gives me a error, saying that the number of arguments is wrong.
    How could I do this?

    2) Then, i have some particular cells in which i have writed a string like “Range(“AM98″)”, can I use this cells as parameters?
    Example:
    Dim Elemento_Corrente As String
    Elemento_Corrente = “Range(“AM98″)”
    OpenSolver.AddConstraint WSheet.Range(Elemento_Corrente), RelationEQ, RHSFormula:=”1″, sheet:=WSheet

    Thank you very much, this will be foundamental for my my degree thesis.
    Mattia

    Reply

    1. You should look at following post (in older comments section)
      —–
      Jack says:
      July 7, 2015 at 3:16 am
      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.

      Reply

  6. I want to add a constraint that show that a certain cell (e.g. C12) should be equal to another cell (e.g. D24).
    How can I add this constraint? I saw that RelationGE and RelationLE works for inequalities, but what is the relation when I want an equality? Does “RelationE” exist?

    Reply

  7. Hi,

    I am currently working on a model in which I have to run 6-7 iterations by changing the input data manually and then re-run the model to get the result. Each time, I need to change the decision variables range, constraints range etc.
    I would like to automate the process using VBA. To do that, OpenSolver.xlam installation and referencing to Opensolver is sufficient or I need to install anything more like ‘Module OpenSolverAPI’.

    Also, considering the dynamic range of different constraints and inputs, would I be able to code Loop using VBA for Open solver to automate it? If yes, kindly share one or two such VBA automated Opensolver model code examples using the loop for reference. If you provide, Few more examples of VBA models for Open solvers that would be great for me to learn and start VBA for the open solver.

    Kindly help me!

    Reply

  8. I have built a rather large model, tested, tested and then tested again… For some reason the variables I am getting for solving the model are float (with decimals). Any way to force OpenSolver to return integers only for variables? I cannot have 0.27465 chickens, I need either none or a whole, live one 🙂

    Thank you,
    Marc Fontaine

    Reply

  10. I defined the variables as follows:

    OpenSolver.SetDecisionVariables TestSheet.Range(Cells(26, 2), Cells(30, 11)), Sheet:=TestSheet
    OpenSolver.SetDecisionVariables TestSheet.Range(Cells(31, 2), Cells(31, 11)), Sheet:=TestSheet
    OpenSolver.SetDecisionVariables TestSheet.Range(Cells(26, 12), Cells(30, 12)), Sheet:=TestSheet

    And I got the following error message:
    A range of cells are specified as bin or int that are not decision variables. OpenSolver does not support this.

    I opened OpenSolver – Model, and I noticed in the “Variable Cells” only cells L26:L30 are declared as variables. Why are the other variables missing?

    Reply

  11. I got the following error message:

    A range of cells are specified as bin or int that are not decision variables. OpenSolver does not support this.

    I can’t figure out what’s wrong with the code. Hope you can help.

    ‘OpenSolver
    Dim WSheet As Worksheet
    Set WSheet = Sheets(“Calculations”)

    OpenSolver.ResetModel Sheet:=WSheet

    ‘Objective Definition
    OpenSolver.SetObjectiveFunctionCell WSheet.Cells(11 + 2 * NoAgents, ConstraintColumn + 4), Sheet:=WSheet
    OpenSolver.SetObjectiveSense MinimiseObjective, Sheet:=WSheet

    ‘Variables Definition
    OpenSolver.SetDecisionVariables WSheet.Range(Cells(6 + NoAgents, 2), Cells(6 + 2 * NoAgents – 1, 1 + NoFlights)), Sheet:=WSheet
    OpenSolver.SetDecisionVariables WSheet.Range(Cells(6 + 2 * NoAgents, 2), Cells(6 + 2 * NoAgents, 1 + NoFlights)), Sheet:=WSheet

    ‘Constraints Definition
    OpenSolver.AddConstraint WSheet.Range(Cells(6 + NoAgents, 2), Cells(6 + 2 * NoAgents – 1, 1 + NoFlights)), RelationBIN, Sheet:=WSheet
    OpenSolver.AddConstraint WSheet.Range(Cells(6 + 2 * NoAgents, 2), Cells(6 + 2 * NoAgents, 1 + NoFlights)), RelationGE, RHSFormula:=”0″, Sheet:=WSheet
    OpenSolver.AddConstraint WSheet.Range(Cells(6 + 2 * NoAgents, 2), Cells(6 + 2 * NoAgents, 1 + NoFlights)), RelationLE, RHSFormula:=”2″, Sheet:=WSheet

    OpenSolver.AddConstraint WSheet.Range(Cells(7 + 2 * NoAgents, 2), Cells(7 + 2 * NoAgents, 1 + NoFlights)), RelationGE, WSheet.Range(Cells(8 + 2 * NoAgents, 2), Cells(8 + 2 * NoAgents, 1 + NoFlights)), Sheet:=WSheet
    OpenSolver.AddConstraint WSheet.Range(Cells(12 + 2 * NoAgents, 1), Cells(12 + 2 * NoAgents + 9999, ConstraintColumn – 1)), RelationLE, RHSFormula:=”1″, Sheet:=WSheet
    OpenSolver.AddConstraint WSheet.Range(Cells(12 + 2 * NoAgents, ConstraintColumn), Cells(11 + 2 * NoAgents + ConstraintRow, ConstraintColumn)), RelationLE, RHSFormula:=”1″, Sheet:=WSheet

    OpenSolver.RunOpenSolver Sheet:=WSheet

    Reply

  12. I am trying to set up a constraint where the the values in cells B5 and C5 should be greater than or equal to zero.

    OpenSolver.AddConstraint TestSheet.Range(“B5:C5”), RelationGE, 0, Sheet:=TestSheet

    I don’t want the RHS range to refer to cells in the worksheet. Is it possible?

    Reply

Leave a Reply

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