Forum Discussion
Solver VBA doesn't apply all constraints
I have the following VBA code: SolverReset
SolverOk SetCell:="$C$27", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$22:$C$24", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$C$24", Rela...
TSDutton Similar how? That's rhetorical. The point is: describe __your__ problem.
And I suspect that you really don't care if JoelF514 per se found a solution. Instead, you would like someone -- anyone -- to solve __your__ problem.
When you describe your problem, don't make the mistake that JoelF514 did. Be sure to provide sufficient information that allows us to reproduce your problem. (FYI, I could not duplicate the problem with the limited informtion that JoelF514 provided.) Usually, that means all formulas in any cell that the objective cell depends on, directly or indirectly.
The best and perhaps simplest way to do that is by attaching an example Excel file (redacted) that demonstrates your problem.
Finally, be sure to let us know what version of Excel you are using, and on what platform (PC or Mac). And if you are using Office 365, my understanding is: it is useful to know the "channel" and the build version.
Here is my current solution to this problem based on literally years of doing Solver VBA development. It's a hybrid worksheet/VBA approach that takes advantage of behaviors that are partly empirically determined:
First, I'm suggesting a problem description that raises it up a level of detail:
Excel Solver implementation has a bug in SolverAdd.
The problem observed is that some constraints expressed using SolverAdd are ignored - they aren't applied and they aren't found in the worksheet Solver Parameters dialog.
I believe that trying to characterize the problem further may do more harm than good as we see a variety of "this worked but...." statements. So here seems to be a workable solution:
SETUP
1) Open the Data/Solver dialog "Solver Parameters".
2) Set up the entire problem in the dialog.
3) Run the entire problem from the dialog.
4) SAVE the problem in the same worksheet (since problems are tied to individual worksheets).
5) Save the workbook.
Now, when you close and reopen the workbook, and/or Excel, and open Data/Solver, you should see your setup unchanged in the Solver Parameters dialog.
WORKING WITH VBA
1) You can try anything you like here as far as I know except don't use SolverAdd or, at least, don't use SolverAdd for expressing constraints.
2) Particularly, you CAN use code like this:SolverOk SetCell:="$I$2", MaxMinVal:=1, ValueOf:=0, ByChange:= _ "$EI$3:$EI$16,$EU$3:$EU$36", Engine:=3, EngineDesc:="Evolutionary" SolverSolve UserFinish:=TrueIn doing this make sure that the ByChange:=xxxxxx matches the Solver Parameters dialog entry for "By Changing Variable Cells:"!!
A good thing about this approach is that you can replace the objective cell in SetCell;=
(I use this to vary the inner Solver setup in order to "kick" the problem forward. For example, if the primary objective is to maximize a dollar amount then one might also have a cell that shows the percentage gain in that same dollar amount. Then maximize the percentage gain for a different slant on the problem. This may seem a trivial change but the Solver doesn't treat it that way. There are surely more sophisticated versions of this).
Using this approach, there can be different subs in VBA that are executed from buttons on the worksheet; each for different objective cells.
