CODEWITHSUNDEEP
excelvbaoptimizationconstraintssolver
Mary
Mary

Reputation: 788

Excel Solver Ignoring Constraint in VBA

I am trying to find a maximum return for a simple portfolio using Solver. Using Solver in the worksheet directly works sensibly, however it does not when the commands are set in VBA. Instead (as you can see from the screengrab)it ignores one of the constraints (that the sum of weights calculated in T10 should =1). Interestingly it works fine if I change the third line to say:

SolverAdd CellRef:="$T$10", Relation:=2, FormulaText:="100"

Or any other integer other than "1". (It may also be ignoring the other constraint but I cannot check this). The table looks like this: enter image description here

And my code is:

Sub FindRange()

                SolverReset
                SolverOk SetCell:="$T$7", MaxMinVal:=1, ValueOf:="0", ByChange:="$O$10:$R$10"
                SolverAdd CellRef:="$T$10", Relation:=2, FormulaText:="1"
                SolverAdd CellRef:="$O$10:$R$10", Relation:=3, FormulaText:="0"
                SolverSolve UserFinish:=True
                SolverFinish KeepFinal:=1
                Range("T9").Value = Range("T7").Value
           End Sub

Any suggestions gratefully welcomed!

Upvotes: 3

Views: 10877

Answers (4)

Hebran Aguayo
Hebran Aguayo

Reputation: 1

Adding an "=" as sevenkul suggested worked for me.

SolverAdd CellRef:="$F$13", Relation:=2, FormulaText:="=1"

Prior to that I was also getting the missing constraint in the solver window. Adding the constraint manually from the worksheet worked fine, but would not show up after running VBA code (due to a SolverReset call). I also think the issue is not limited to the value 1. I also tried with 2 and had a similar result.

Oddly enough after running the suggested fix once, I no longer need the extra "=" in the FormulaText. I closed and reopened the workbook and do not need the workaround. Running it once seems to do it. Not sure if this will need to be done on every new workbook using a solver in the code.

Upvotes: 0

user2092957
user2092957

Reputation: 311

I think there is a bug here whenever the value is exactly 1. Other postings state that the above solution (putting the value into a cell) is unreliable. I have found it does not work a my code always refers to a cell which holds the constraint limit. My (crude) solution is to shift the limit value by 1 part in 10^12 or lower in the appropriate direction which sort of makes the constraint into a < or > rather than a <= or >=. So rather than:

SolverAdd CellRef:=Range("SolverParam").Address, Relation:=3, _ FormulaText:=Range("SolverConstraint").value

Use:

SolverAdd CellRef:=Range("SolverParam").Address, Relation:=3, _ FormulaText:=Range("SolverConstraint").value + Abs(Range("SolverConstraint").value) * 1e-12

And use the opposite sign for Relation:=1

In this trivial example SolverParam is a single cell parameter to be adjusted and SolverConstraint is a single cell lower bound.

This is the only consistent approach I can foresee to have a uniform handling of all values

On further looking I found another solution from the web

FormulaText:="=" & Range("SolverConstraint").value

Seems to work reliably

Upvotes: 1

Priya
Priya

Reputation: 1

I had the exact same issue. I solved it in the following way: Just type FormulaText:=1 without the quotes for 1.

Upvotes: 0

Bo Lu
Bo Lu

Reputation: 641

Found a work around for the bug. For the flag "FormulaText:=1". instead of using 1, use a reference to any cell with the value 1 instead.

I.e., Change "FormulaText:=1" to "FormulaText:=$H$5" where $H$5's value is 1

Upvotes: 3

Related Questions