Excel Solver Constraint lost when using VBA

[popper]

I am using Excel Solver under control of VBA. When I have a constraint with an upper and lower bound, one of these is lost. For example if A1 contains a decision variable and I have

A1<=1
A1>=0

the second one simply disappears when using VBA. I would really appreciate advice on how to get around this apparent bug.

Thanks

 

[electricpete]

You can set your constraints right in vba. For example

SolverOk SetCell:="$E$19", MaxMinVal:=1, ValueOf:="54", ByChange:="$D$19"
SolverAdd CellRef:="$D$19", Relation:=1, FormulaText:="2"
SolverAdd CellRef:="$D$19", Relation:=3, FormulaText:="-3"
SolverOk SetCell:="$E$19", MaxMinVal:=1, ValueOf:="54", ByChange:="$D$19"
SolverSolve

Above has two constraints on cell D19. Worked fine for me.

Is the problem that you it to remember constraints from a previous run and those appear to be disappearing? Can you add them in vba?

Is this behavior different in vba then when you run solver directly thru excel?

 

[electricpete]

I forgot to say: if you are going to define your problem in vba, you need a reset command first to remove previous problem defintiion:

SolverReset

 

[popper]

Hi Thanks for your reply. No, I did everything you did, including the reset and it was still problematic.

However, since posting my problem, I did manage to get around it. Perhaps I was in error but this is what I was doing originally for a multiple set of decision variables:

SolverAdd CellRef:="$D$19:$D$21", Relation:=1, FormulaText:="2"
SolverAdd CellRef:="$D$19:$D$21", Relation:=3, FormulaText:="-3"

That is, I thought I was placing the same constant constraint for each of them and this would not work. Now, I place the constraint constants 2 and -3 in a range (say columns E and F) and then do the following:

SolverAdd CellRef:="$D$19:$D$21", Relation:=1, FormulaText:="$E$19:$E$21"
SolverAdd CellRef:="$D$19:$D$21", Relation:=3, FormulaText:="$F$19:$F$21"

This works. However, if I do each decision variable separately, as shown in my first post, the problem remains.

I am using Excel 2003 and cannot find any documentation about this problem.

But thanks again.