Date: 2025-03-06 02:31:43
Score: 0.5
Natty:
The workaround that I came up for missing constraints, does this:
- Don't use SolverAdd in VBA. That appears to be how this issue starts.
- Instead, use the Solver Parameters dialog that opens in a worksheet under Data/Solver.
- To make it easier to deal with, structure the variables and constraints in a list in the worksheet:
Name | Type | Lower Bound | Value | Upper Bound
For Binary types, only the value is necessary.
For Integer and (non-integer constrained) types, the bounds may be necessary.
(I use Evolutionary mode usually and make them required).
- With this done, you can select the Value cells to create ranges to paste into the Solver Parameters dialog in the appropriate places. You will have to specify Binary, Integer for those. For integer, you will also need to enter the <= constraints and the >= constraints, just the same as with non-integer constraints.
This may seem counter to what you want to do if your intent is to use VBA.
But, fortunately, this structured approach works well with VBA implementations because the Solver Parameters dialog information sticks once entered.
So, now one is able to write optimization subs and to use SolverOk and SolverSolve in VBA. You can change the SetCell entry if appropriate, just make sure to enter the ByChange:= range the same as is entered in the Solver Parameters dialog.
This is the only solution I've found after a lot of effort, study and research. I'm running large problems consistently without trouble as long as I don't use SolverAdd. I note that others have found positive results doing something similar.
It's worthy of note that the right-hand-side RHS of the constraints can be entered as ranges as set out above. I had hoped this would help use SolverAdd but it didn't. The approach using ranges on both the LHS and RHS reduced the number of constraint entries from around 80 down to 6. This makes it much easier to debug!!
Reasons:
- Long answer (-1):
- No code block (0.5):
- Low reputation (1):
Posted by: fred