Excel Solver Macro: Contraints Get Lost! - Please Help

[david.i.schroeder]

Hey everyone,

I am encountering a problem while running the following excel solver
macro which I recorded:

Sub Macro1()
SolverReset
SolverOk SetCell:="$BK$21", MaxMinVal:=1, ValueOf:="0", ByChange:=
_
"$BQ$17:$BQ$19"
SolverAdd CellRef:="$BQ$17:$BQ$19", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$BQ$17:$BQ$19", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$BQ$20", Relation:=2, FormulaText:="1"
SolverOk SetCell:="$BK$21", MaxMinVal:=1, ValueOf:="0", ByChange:=
_
"$BQ$17:$BQ$19"
SolverSolve
End Sub

While the solver works fine when run manually, the macro does not
arrive at the correct result. Looking up solver after the macro run
unveils that only one of the constraints (the second one) is loaded
into solver by the macro. The two other constraints miraculously
disappear. Any suggestions what I might do to fix this problem? I
really need it to work as it forms part of my master thesis :-(

Thank you very much in advance!

David

 

[Dana DeLouis]

While the solver works fine when run manually,

Just a guess of course: Excel 2000?
The Solver add-in may not work when you start Solver by using a macro in
Excel 2000

http://support.microsoft.com/kb/821430/en-us

Just some ideas:

You have SolverOk listed twice. I'd drop the last one.
You can drop ValueOf, since it's ignored.
ie: SolverOk SetCell:="$BK$21", MaxMinVal:=1, ByChange:= "$BQ$17:$BQ$19"

Are you trying to make Q17:Q19 vary between 0 & 1, or make them either 0 or
1?
I'll assume "vary between 0-1" as posted.

I'm only guessing here on your model, so I may be wrong.
You want x+y+z = 1.
You want Solver to change x,y,z, with the constraint that they total 1.

As Solver changes x&y, it's almost impossible for Solver to guess z that
will make the equation sum to 1. Added to the problem is that fact that any
Sum will most likely not trigger a True for equality. (x+y+z = 1
....exactly! )
Also, as Solver gets close to guessing z, x&y get changed again, and Solver
gets confused.

On technique to work around this is to try to avoid equality functions.
You can do this here, for example, by solving for z.
With x+y+z =1, then z = 1-x-y.

So, have Solver change x & y.
Make z a function now with: =1-x-y.

Constraints are
x,y,z >=0
x,y,z <=1.

Again, just some guesses.

 

[david.i.schroeder]

Hey Dana,

thank you very, very much for your suggestions! Although your first
guess about Excel 2000 was wrong (actually using Excel 2003), your
second suggestion did the trick.

Thank you once again.

David

 

[Dana DeLouis]

second suggestion did the trick.

Hi. Glad it's working. :>) Nothing like a good guess.
For my own education, was the solution the dropping the second SolverOk?
The reason I ask is that others have also posted code where there were two
SolverOk's.
They also reported that they "recorded a macro" to get the code.
If so, I wonder if there is something that triggers an error with the
recording of Solver macros?

 

[Jon Peltier]

Dana -

Whenever I record a macro using Solver, if it records setting of constraints
it puts SolverOK both before and after the constraints. I usually delete the
first, but just to streamline the code. I haven't noticed any issue with
running the code with either or both SolverOK statements, it just seemed
wasteful to have both, and seemed to be logical (like that should matter) to
put SolverOK after the SolverAdd's.

FYI, I recently posted a page on using Solver with VBA, and I'd appreciate
any suggestions from someone with your knowledge about Solver. If you have
any comments, use the email link at the bottom of the page:

http://peltiertech.com/Excel/SolverVBA.html

- Jon