Solver Excel VBA Integer Constraint Ignored

Question

I've looked through as many examples on here as I think there are but for some reason in my code the integer portion of it is being ignored. Underneath the 1st "SolverAdd, you'll see the other 2 that I've tried to use as well but each time the integer requirement is ignored. Any idea what I am doing wrong ? Thanks!

SolverReset
SolverOk SetCell:="$T$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$R$8:$R$13" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
'Solver ignores integer requirement for the reference range
SolverAdd CellRef:="$R$8:$R$13", Relation:=4, FormulaText:="integer"
'Have tried this: SolverAdd CellRef:="$R$8:$R$13", Relation:=4
'Have also tried this: SolverAdd CellRef:="$R$8:$R$13", Relation:=4, FormulaText:=0
SolverSolve True

Have also tried this:

SolverReset
SolverOk SetCell:="$T$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$R$8:$R$13" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
'Solver ignores integer requirement for the reference range
SolverAdd CellRef:=Range("$R$8:$R$13"), _
Relation:=4
SolverSolve True

In the Solver dialog, Options, All Methods, is "Ignore Integer Constraints" checked? — Excelosaurus
– Excelosaurus, Commented Sep 29, 2020 at 21:12
It is not checked, do I need to reset the options within the VBA for solver? Or do those options set there role over into the VBA automatically ? — Aspiring Developer
– Aspiring Developer, Commented Sep 29, 2020 at 21:13
This is so bizarre - I added another "SolverSolve" at the end of the statement and it is now keeping them as integers. However it is ignoring the MaxMinVal:= 1 criteria — Aspiring Developer
– Aspiring Developer, Commented Sep 29, 2020 at 21:19

Aspiring Developer · Accepted Answer · 2020-09-29 21:37:36Z

1

I thought the constraint was built into the "MaxMinVal" portion of the solver code.

I manually added both constraints with the range of T4 being maxed a 1.000000000001 since just having "1" causes issues from what I've seen.

SolverReset
SolverOk SetCell:=Range("$T$4"), MaxMinVal:=1, ByChange:=Range("$R$8:$R$13") _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:=Range("$R$8:$R$13"), _
Relation:=4
SolverAdd CellRef:=Range("$T$4"), _
Relation:=1, FormulaText:="1.00000000001"

FormulaText:=0 SolverSolve True

answered Sep 29, 2020 at 21:37

Aspiring Developer

68013 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Solver Excel VBA Integer Constraint Ignored

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related