Solver Code Doesn’t Like Shapes
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
SolvSolve 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
Analyze
End If
End Sub
Now the constraints are added and (I hope) the results are correct.
MacroMan:
Hey Dick,
How are ya? I took the leap to become a professional VBAer remember? It's going great, I'm holding my own and I luv coding and solving problems professionally. Heck I was doing this for fun before. I was wondering if you know how to convert Excel worksheets to pdf files via Excel VBA. I'll be looking for your reply at work! Keep up the good work on this site. I dig the new colors by the way : )
-MacroMan
21 June 2007, 7:09 pmDavid:
I have been running the Premium Solver from a command button on the spreadsheet with no problems. And the results generated match the results obtained the same model developed in GAMS. I run 20 solver runs on the one page and I have found the solverreset is vital each time you run the code as it clears solvers thought patterns out. The other option I have found to be useful it to clear the cells that solver changes as that then clears the presolved solution and this is important.
One possibility of error I can see is that you may not have defined which algorithm solution to use. But I can not remember if that is required in the base version.
The use of the 'AssumeNonNeg is true' is probably where you had the greatest saving in time as it reduces the search space to only positive numbers. Depending upon your objective function this would be a huge saving in time.
For Solver I have found that you have to be very careful with your model on the spreadsheet, if you make a mistake it will do its best to find a solution but you may have sent it down an infeasible path. I have found a lot of advantages to carefully set up the spreadsheet to minimise the time it takes solver to find a solution.
21 June 2007, 7:58 pmMikeC:
MacroMan: good to hear things are going well for ya!
Excel 2007 has the ExportAsFixedFormat option to export to .pdf, but previous versions don't cover it. I've never been able to find a way of doing this within Excel itself to manage this and nor has anyone I've come across.
Full version Adobe Acrobat includes extra references that can be referenced from VBA to do this, but, of course that costs £££! Haven't done this myself, so I don't have the details as to exactly what would need doing once it's in there, but I know it does give the option somehow.
(Dick - apologies for "jumping the gun" here, and I'm sure you or one of the other regulars may be able to offer MM a more complete answer - if there is one, it'd be great to see it in a future post!)
22 June 2007, 4:28 amjkpieterse:
Macroman: pdf995 only costs you $ 9.95 and does the trick.
22 June 2007, 5:25 amJon Peltier:
MacroMan -
Glad it's working out. I changed careers three years ago: I was laid off and couldn't stomach that whole "Corporate" dead end.
Ken Puls (http://excelguru.ca/) has developed a system of code for using VBA to drive the free PDF utility PDFCreator. The pieces of it that I've used have been just fine.
22 June 2007, 6:32 amjkpieterse:
Jon: I've done same for PDF995.
22 June 2007, 7:33 amAndrew Reynolds:
Solver uses a whole host of hidden names which you might find useful - easily viewable using name manager. "VBA for modellers" - a management science/operational research focused book is a good source to learn more about using this add-in with VBA.
22 June 2007, 7:57 amKen Puls:
Hey guys,
If you're interested in starting with PDFCreator, the best page on my site to get you started is here: http://www.excelguru.ca/node/21
It contains a link to download the utility (free), as well as examples of how to use it in various Excel scenarios. There's a few other PDF articles at my site as well that you can link to from there.
Hope it helps!
22 June 2007, 4:32 pmMacroMan:
Thank you everybody for the info, especially to Jon Peltier for directing me to Ken Pul's code, and Ken Puls for writing the code of course. I haven't tried it yet but it looks great. This should impress my boss.
-MacroMan
22 June 2007, 6:38 pmMacroMan:
Wow! Works great! Thanks again, VBA and Excel is amazing.
-MacroMan
23 June 2007, 8:35 amross:
I've always been quite impressed with solver and it's performance - it's xlm and VBA after all, but for big problems I've started to used LPSolve for for speed and size. The other thing that "might" speed up big problems (although i think not in your case Dick) is to rescale the problem.
24 June 2007, 6:29 amMikeC:
Ken- that's awesome. Thanks!
25 June 2007, 1:53 amKeith Halford:
Programming with the solver causes grief when multiple versions of Excel are used becuse the library keeps moving. I took the install on open and uninstall on close approach for the problem documented in http://pubs.usgs.gov/sir/2006/5024/
30 June 2007, 8:34 pmTushar Mehta:
Dick:
I haven't used Solver for complex analysis in several years so I don't know how -- if -- it has changed in the last decade or so. Before that, it worked fairly well given that it was free.
The biggest issue with Solver -- and with any other optimization method / package -- is that of the underlying optimization principles from the field of Operations Research.
If a problem is / can be defined as a linear problem it can be solved a lot faster than a non-linear problem. In addition, a solution to a linear problem is guaranteed to provide the best possible solution.
Unless the folks who developed Solver have done something really screwy with the software, I don't remember it having any interactions with shapes in the worksheet. Is it possible that there is code that reacts to changes to the worksheet? Do keep in mind that Solver assumes that once it starts it is the only entity changing the worksheet. If you have any event procedures tinkering with the workbook, Solver will get confused.
FWIW, the stuff you are doing (deleting and re-entering constraints between each scenario) does help. I don't know why but using SolverReset and then reestablishing the optimzation problem works wonders. As will the below.
For an introduction to linear programming, here's a link courtesy Jake Marx: http://www-unix.mcs.anl.gov/otc/Guide/faq/linear-programming-faq.html
You can also look at a few posts by me (I am sure I've posted a lot more on Solver and its optimal use but I cannot find any of those other posts):
http://groups.google.com/group/microsoft.public.excel.programming/tree/browse_frm/thread/66d36e97de2345a7/5ff6e53976fd55c7?rnum=21&hl=en&_done=%2Fgroup%2Fmicrosoft.public.excel.programming%2Fbrowse_frm%2Fthread%2F66d36e97de2345a7%2F2737e222ea5f5cb3%3Fhl%3Den%26rnum%3D2%26prev%3D%252Fgroups%253Fq%253Dsolver%252Babs%252Bgroup%253Amicrosoft.public.excel.*%252Bauthor%253Atushar%2526hl%253Den%2526selm%253DMPG.1480676770168303989c49%252540msnews.microsoft.com%2526rnum%253D2%26#doc_9b3284d04fb388ff
http://groups.google.com/group/microsoft.public.excel.programming/tree/browse_frm/thread/66d36e97de2345a7/5ff6e53976fd55c7?rnum=21&hl=en&_done=%2Fgroup%2Fmicrosoft.public.excel.programming%2Fbrowse_frm%2Fthread%2F66d36e97de2345a7%2F2737e222ea5f5cb3%3Fhl%3Den%26rnum%3D2%26prev%3D%252Fgroups%253Fq%253Dsolver%252Babs%252Bgroup%253Amicrosoft.public.excel.*%252Bauthor%253Atushar%2526hl%253Den%2526selm%253DMPG.1480676770168303989c49%252540msnews.microsoft.com%2526rnum%253D2%26#doc_2f517586282adc6d
and
http://groups.google.com/group/microsoft.public.excel.programming/tree/browse_frm/thread/a0fa2172cd819fd7/17e36a69cc63a41b?rnum=11&hl=en&_done=%2Fgroup%2Fmicrosoft.public.excel.programming%2Fbrowse_frm%2Fthread%2Fa0fa2172cd819fd7%2F396894bcec7235c9%3Fhl%3Den%26rnum%3D3%26prev%3D%252Fgroups%253Fq%253Dsolver%252Babs%252Bgroup%253Amicrosoft.public.excel.*%252Bauthor%253Atushar%2526hl%253Den%2526selm%253DMPG.168fda325913c9b98a3f3%252540msnews.microsoft.com%2526rnum%253D3%26#doc_396894bcec7235c9
You may also want to look for posts by Dana DeLouis and possibly Tom Ogilvy.
1 July 2007, 1:56 pmJon Peltier:
Keith -
I've addressed the headaches of multiple Excel/Solver versions by not setting a reference to Solver, and using Application.Run to invoke the Solver routines. It's a step backwards, but the alternative was a complex maze of installing and uninstalling add-ins, setting and unsetting references, trying to instruct unsavvy users to adjust their security settings and showing them how to allow access to the VB project, etc.
I've always intended to post this approach in a web page on my site, but haven't gotten around to it. If there's interest, maybe I'll try to free up a couple hours to write it up.
2 July 2007, 8:41 amKeith Halford:
Jon,
Going backwards sounds brilliant. I have experienced the complex maze and dislike it. Details for a less troublesome approach of using Solver within VB would be appreciated greatly.
2 July 2007, 11:19 amJon Peltier:
I've posted the web page on Using Solver in VBA:
http://peltiertech.com/Excel/SolverVBA.html
Email me with any errors or problems.
7 July 2007, 2:24 pmKeith Halford:
Thank you. Your solution worked like a charm.
7 July 2007, 3:43 pmJoe Ferreira:
Thank you much
I was frustrated because my constraints in solver would not register,actually had 3sets and only one set (the set with
5 October 2007, 3:36 pm