Deleting Solver Constraints in VBA
Posted by Dan Henry on June 07, 2001 2:45 PM
I wrote the following macro to automate the Solver:
SolverOk SetCell:=ActiveCell.Offset(0, 0), MaxMinVal:=3, ValueOf:="0.95", ByChange:=ActiveCell.Offset(0, -1)
SolverAdd CellRef:=ActiveCell.Offset(0, -1), Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1)
SolverAdd CellRef:=ActiveCell.Offset(0, -1), Relation:=3, FormulaText:=ActiveCell.Offset(0, -3)
SolverSolve
SolverDelete CellRef:=ActiveCell.Offset(0, -1), Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1)
SolverDelete CellRef:=ActiveCell.Offset(0, -1), Relation:=3, FormulaText:=ActiveCell.Offset(0, -3)
When run, the macro adds the two constraints called for by the SolverAdd lines (except that it replaces the cell address with the cell value in the right-hand side of the constraint -- for example, instead of "$B$4<=$B$3", I get "$B$4<=0.2512"; it works fine, but it's kind of unexpected).
My problem is that the SolverDelete lines don't delete the constraints. If I run the macro over and over, I end up with a long list of constraints, which I have to delete by hand. Anyone figured out how to delete solver constraints using VBA?
Thanks
Posted by Mark W. on June 07, 2001 3:21 PM
Might it be easier to edit and then load a model
that was "tucked away" somewhere?
Posted by Dan Henry on June 07, 2001 3:47 PM
Feeling kind of dense here - not sure what you mean. I know you can save solver results as a scenario. Is that your point? Something else?
Dan
Posted by Mark W. on June 08, 2001 7:58 AM
Dan, this is a quick response 'cause I'm heading
into a 4-hour meeting very soon. While helping
someone else I posted a Solver model at...
When you press Solver's [ Option ] button you'll
find a [ Save Model... ] and [ Load Model... ]
button. I'm thinking that there are comparable
commands in VBA, and that it might be easier to
maintain a model on a worksheet and then load it
in 1 fell swoop! If you're interested in pursuing
this setup this Solver model, Save and Load the
model and see what you get.
Posted by Dan Henry on June 11, 2001 7:21 AM
The Answer!
I found the answer elsewhere (maybe this post will clarify my question). I had to add '.AddressLocal' to the end of each cell reference in the SolverAdd and SolverDelete lines:
SolverOk SetCell:=ActiveCell.Offset(0, 0), MaxMinVal:=3, ValueOf:="0.95", ByChange:=ActiveCell.Offset(0, -1)
SolverAdd CellRef:=ActiveCell.Offset(0, -1).AddressLocal, Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1).AddressLocal
SolverAdd CellRef:=ActiveCell.Offset(0, -1).AddressLocal, Relation:=3, FormulaText:=ActiveCell.Offset(0, -3).AddressLocal
SolverSolve
SolverDelete CellRef:=ActiveCell.Offset(0, -1).AddressLocal, Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1).AddressLocal
SolverDelete CellRef:=ActiveCell.Offset(0, -1).AddressLocal, Relation:=3, FormulaText:=ActiveCell.Offset(0, -3).AddressLocal
