Solver in VBA does not add the integer constraint

Question

I was using VBA code to run solver. Below is my code. I want cells C87:K93 to be integers, however, the constraint does not get added into Solver so the values I get are all decimals. May I know how I can change the code so that the integer constraint get taken into account?

Sub Solve()
   SolverReset
   SolverAdd CellRef:="$C$87:$K$93", Relation:=4, FormulaText:="integer"
   SolverAdd CellRef:="$C$87:$K$93", Relation:=1, FormulaText:="$C$48:$K$54"
   SolverAdd CellRef:="$L$87:$L$93", Relation:=1, FormulaText:="$M$87:$M$93"
   SolverAdd CellRef:="$C$87:$K$93", Relation:=3, FormulaText:="0"

   SolverOk SetCell:="$N$95", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$87:$K$93"
   SolverSolve UserFinish:=True
End Sub

Below is the link to the picture of Solver after running the code, the integer constraint just doesn't appear

Thanks so much for helping

Try to do everything by hand, without VBA. And see if the constraint disappear. Remember that you can have a integer constraint and even so you get decimal results. That's the way solver works. If it is not possible for solver to get an integer solution, it will try the possible one. That means there is no integer solution or you model is not correctly designed. (that don't means that the constraint will disappear from the definitions) — CRondao
– CRondao, Commented Apr 9, 2014 at 15:49
I know it's been a while, but you can't have a FormulaText for Relations 4,5 and 6 — SeanC
– SeanC, Commented Sep 10, 2014 at 16:44

Axel · Accepted Answer · 2017-09-21 14:45:49Z

I recently encountered the same problem. Even without having the FormulaText at the end of the integer constraint.

This should work:

Sub Solve()
   SolverReset
   SolverOk SetCell:="$N$95", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$87:$K$93"
   SolverAdd CellRef:="$C$87:$K$93", Relation:=4
   SolverAdd CellRef:="$C$87:$K$93", Relation:=1, FormulaText:="$C$48:$K$54"
   SolverAdd CellRef:="$L$87:$L$93", Relation:=1, FormulaText:="$M$87:$M$93"
   SolverAdd CellRef:="$C$87:$K$93", Relation:=3, FormulaText:="0"

   SolverSolve UserFinish:=True
End Sub

When applying an integer constraint (SolverAdd) to cells that are not part of the decision variables (ByChange), the solver prompts:

"Integer Constraint Cell Reference must include only Variable Cells."

This is not the case in your example, but the setting the objective function still plays a role. I assume that if you add the integer constraint before you add the SolverOk, the solver does not know the decision variables (ByChange) yet and therefore does not include the integer constraint.

Collectives™ on Stack Overflow

Solver in VBA does not add the integer constraint

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