I was recently using the Solver Add-in through VBA, something I haven’t done much of in the past. I definitely learned some things. I thought I would document those lessons even though I haven’t fully tested them and I don’t fully understand why they do what they do.
Lesson 1: Installing the add-in using Tools > Add-ins in Excel does not expose the Solver object model to my project. I still had to set a reference to SOLVER in the VBE (VBE: Tools > References).
Lesson 2: Solver can be really slow. I processed 50 scenarios with four constraints each and it took about eight minutes. Yikes. I made a few changes to how I setup the Solver code and I’m doing the same thing in about 17 seconds. I’m not sure which of the modifications reduced the time. The first thing I did was reset Solver before setting up the scenario using SolvReset. Next, I removed the constraints that kept cells above zero and used
Finally, and as a consequence of reseting before each iteration, I re-added all of the constraints each time. In the code below, I’m looking for the Max. I needed to do the exact same scenario but look for the Min. When I just changed Max to Min and let the constraints carryover from the previous setup, it was considerably slower. I thought for sure it would have been faster not to wipe them out and re-add them, but that was not my experience.
Here’s how the code ended up, roughly:
SolvOk SetCell:=.Range(“MyRange”).Address, MaxMinVal:=1, _
ByChange:=.Range(“rngFirst”).Address & “,” & .Range(“rngSecond”).Address
SolvAdd CellRef:=“$A$1”, Relation:=2, FormulaText:=“1” ‘A1=1
SolvAdd CellRef:=.Range(“rngFirst”).Address, Relation:=1, FormulaText:=“1.0” ‘rngFirst <= 1
SolvAdd CellRef:=.Range(“rngSecond”).Address, Relation:=1, FormulaText:=“1.0” ‘rngSecond <=1
SOLVoptions , , , , , , , , , , , True ‘Options – assumenonneg = true
Lesson3: The code ran from a Forms commandbutton on a worksheet. I wasn’t getting the correct results. It was easy to see that the results were wrong because rngSecond was over 1 million when I had clearly constrained it to <=1. I interrupted the code right after SolvSolve and looked at Solver manually through the Excel UI. There were no constraints in there. None. I found on the InterWeb (sorry I don’t recall where) that shapes on a worksheet can cause constraints not to be added.
I removed the commandbutton and put a hyperlink in its place. I set the hyperlink’s target to the cell where it lived so that it essentially did nothing and I added code like this:
If Target.Range.Address = Me.Range(“A2”).Address Then
Now the constraints are added and (I hope) the results are correct.