excelvbaexcel-solver

Excel VBA Solver - Why do I not get a valid solution?

I am trying to solve an optimization using Excel Solver. It works fine when I do it manually. But when I try it in VBA I get the following Error message: Error in modell. Please verify that all cells and contraints are valid.

Sub solv()
Dim rng As Range
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("B1:B3")

SolverReset
SolverOk SetCell:=C5, MaxMinVal:=1, ValueOf:=0, ByChange:=rng, Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:=rng, Relation:=5, FormulaText:="binary"
SolverSolve

End Sub

The calculations also change from automatic to manual when runing the code.

I've tried all sorts of different changes to SetCell, ByChange and CellRef, but nothing works. Anyone who can help med with this?

Excel sheet

Solution

  • If I understand correctly, this seems to work. The macro recorder was actually helpful here:

    Sub solv()
Dim rng As Range
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("B1:B3")

SolverReset
SolverOk SetCell:="C5", MaxMinVal:=1, ByChange:=rng.Address, Engine:=2
SolverAdd CellRef:=rng.Address, Relation:=5, FormulaText:="binary"
SolverSolve

End Sub