Using Excel Solver In VBA

Here is a routine that shows how to use Solver within VBA.

Note: These bits of code are mostly for my own reference, but if anyone else finds them useful all the better.

Sub RunSolver()

    Application.ScreenUpdating = False

    Dim CellsToChange As Range
    Dim TargetCell As Range

    

    Dim StartCell As Range
    Dim EndCell As Range
    
    Dim SetCellString As String
    Dim ByChangeCellString As String
    
    Set CellsToChange = Range("CellsToChange")
    Set TargetCell = Range("TargetCell")

    SetCellString = TargetCell.Address

    Set StartCell = CellsToChange(1, 1)
    Set EndCell = CellsToChange(1, CellsToChange.Columns.Count)
    ByChangeCellString = StartCell.Address + ":" + EndCell.Address
    
    SolverReset
    SolverOK SetCell:=Range(SetCellString), MaxMinVal:=2, ValueOf:=0, ByChange:=Range(ByChangeCellString), Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve UserFinish:=True

End Sub

Sub RunSolver()

Application.ScreenUpdating = False

Dim CellsToChange As Range

Dim TargetCell As Range

Dim StartCell As Range

Dim EndCell As Range

Dim SetCellString As String

Dim ByChangeCellString As String

Set CellsToChange = Range("CellsToChange")

Set TargetCell = Range("TargetCell")

SetCellString = TargetCell.Address

Set StartCell = CellsToChange(1, 1)

Set EndCell = CellsToChange(1, CellsToChange.Columns.Count)

ByChangeCellString = StartCell.Address + ":" + EndCell.Address

SolverReset

SolverOK SetCell:=Range(SetCellString), MaxMinVal:=2, ValueOf:=0, ByChange:=Range(ByChangeCellString), Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve UserFinish:=True

End Sub

Leave a Reply