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.
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
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.
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!)
Macroman: pdf995 only costs you $ 9.95 and does the trick.
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.
Jon: I’ve done same for PDF995.
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.
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!
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
Wow! Works great! Thanks again, VBA and Excel is amazing.
-MacroMan
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.
Ken- that’s awesome. Thanks!
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/
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.
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.
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.
I’ve posted the web page on Using Solver in VBA:
http://peltiertech.com/Excel/SolverVBA.html
Email me with any errors or problems.
Thank you. Your solution worked like a charm.
Thank you much
I was frustrated because my constraints in solver would not register,actually had 3sets and only one set (the set with
I realize this is an old topic, but it seems an appropriate place to put some additional information. Jon Peltier has a nice web page discussing how to run the solver from VBA, but two items are missing. I’m mainly looking at his CheckSolver() function and the RunSolver() sub.
First, with Excel 2007, the solver Add-In file is “solver.xlam,” so one would need to change the “solver.xla!” text in these procedures or add a test for the Excel version. For example,
Dim SolvFile As String
If CLng(Left$(Application.Version, 2)) < 12 Then
‘Excel 2003 or previous
SolvFile = “solver.xla!”
Else
‘Excel 2007 or later
SolvFile = “solver.xlam!”
End If
‘Apply it as:
Application.Run SolvFile & “SolverReset”
I haven’t gotten into Excel 2010, but I presume it is the same as Excel 2007 in this respect.
Second, a small modification to the CheckSolver() function is useful (in some cases at least). I spent several hours trying to figure out why I could manually run Solver and I could run the Solver by entering Application.Run commands in the Immediate pane of the VBA IDE, but the Solver just would not run if I started my code via a Forms button placed on the worksheet. I checked for issues with shapes, quantity of custom number formats, conditional formatting, use of links, use of UDFs, etc. to no avail. But I came across a brief mention about the solver getting focus in a newsgroup posting by Dana DeLouis on installing the Solver. So I altered Jon’s CheckSolver() function by adding the following:
Dim wb As Workbook
Set wb = ActiveWorkbook
‘The body of Jon’s function goes here
wb.Activate
End Function
And (thankfully), this fixed the issue!
That all said, I can start with a new workbook and a fresh instance of Excel, and make a simple solver problem (e.g., x value at the minimum of a quadratic function) and run it with the unaltered CheckSolver() and the equivalent RunSolver() via a Forms button on the worksheet, and it works fine! I suppose one could go nuts trying to figure out the idiosyncrasies of Excel — I’m just happy to find an answer and thought it worth adding here.
I know this is an old thread but I see people are still posting to it and I thought it might help if I threw in a few pointers.
First I have given up on the free version of Solver – it is too flaky for anything other than the worlds most trivial problems and with more than a few variables or constraints even if it tells you it has found a globally optimal solution it quite often hasn’t (and it may fail to in a spectacular way) so using anything it returns in VBA without examining the output in detail manually is very risky.
Resetting the solver is good practice and can result in better/faster solutions – industial quality solvers generally have this as an option which you can chose or not to use because if you are just tweaking some of the variables starting from a previous solution can be beneficial but if you are going from “maximise” to “minimise” it will be a hindrance not a help.
You should use the minimum number of constraints in your model you can – setting bounds to variables reduces the solution space without adding to the constraints which are the computationally intensive part of the solution so if you can do this you should. You shoud also use = constraints rather than = if you possibly can as this is again much less computationally intense. You should also minimise the number of integer (and binary) constraints as these have a huge overhead.
I have used a number of professional LP Solvers with Excel. In my opinion the Lindo “What’s Best?” implementation is the easiest to use with the most intuitive front end and is fairly fast. I have not tried the professional version of Solver though. But all these are very expensive add-ins for Excel and you can’t produce “compiled” versions to ship with your model if you want to disseminate it.
I have recently been using LP Solve which is available as an Excel implementation through a dll. LP Solve is freely available and you can write commercial models that use it providing you keep solver outside your code and dont alter it – you can freely distribute copies. Some kind person has implemented all the lp solve functions so you can call them direct from VBA through a class module. LP Solve is an industrial strength MIP solver which will work with almost any programming language. It requires you to build the model in the solver itself through arrays. It is very powerful and very fast (as it is without any of the excel overhead once it gets to work) but you need to write routines to build your constraint arrays and to write back the solution to Excel. However if you can figure out how to pose your problem as an LP that is trivial. It has all the bells and whistles of a proper industrial grade solver and you can do just about anything you can in other solvers which cost thousands of dollars and it performs just as well if not better in all the cases I have tried. The only downside is that you need to wade through the documentation to do this (and understand what you are trying to do) but it is all on line and getting to grips with the basics is fairly easy.
Thanks Peter – LPSolve downloaded.