Contents
Solver and Conventional VBA
Avoiding Solver Reference Problems
Excel places each report that you generate for a Solver problem in a separate worksheet in the workbook. To generate one (or all) of these reports, select the report type (Answer, Sensitivity, or Limits) from the Reports list box of the Solver Results dialog box. Want to get more from the Excel Solver? Try Analytic Solver Basic for $250/year you get an easy to use guided mode, over 200 examples you can easily.
Preparing Solver for First Use
Solver Links
Solver and Conventional VBA
Solver is a powerful analysis tool, bundled with Excel and used for optimization and simulation of business and engineering models. It can be even more powerful if used in conjunction with VBA, to automate solving of multiple models which use different input parameters and constraints.
In a simple example, there are two factors in B5 and B6. The product (=B5*B6) is calculated in B8. Solver will be used to find the maximum value of the target cell (the product in B8), subject to the constraint that both factors (B5:B6) shall not exceed a value of 4. Select Solver from the Tools menu, and enter the appropriate conditions and constraints in the Solver Parameters dialog.
Click the Solve button, and another dialog indicates whether a solution is found and offers some options.
If you record a macro while you use Solver, you will get something like the following:
SolverAdd adds constraints to the Solver model. SolverOK defines the cell to optimize, how to optimize it, and what cells to change during the Solver optimization. The macro recorder wrote this line twice, so the first occurrence can be removed. To prevent parameters from a different Solver optimization interfering with the macro's optimization, Solver should be reset prior to running, using SolverReset. SolverSolve has an optional UserFinish argument; if UserFinish is False or omitted, the second dialog shown above will ask the user whether to save the optimization, but if UserFinish is True, Solver will end without the dialog. A modified Solver macro is shown below:
When you try to run this macro, you get a compile error. The command SolverReset is highlighted, and the following error message appears.
In order to use a macro based on an installed add-in, you must first make sure that the add-in is installed, then you must set a reference to the add-in in the workbook containing the code that calls the add-in's procedures.
To install an add-in, on Excel's Tools menu, choose Add-Ins. If the add-in is shown on the list, check the box in front of its name. If the add-in is not found, click Browse, navigate to the add-in file*, then when it appears on the add-in list, check its checkbox. Solver was already installed, or we would not have been able to record a macro using it.
*Depending on your Office and Windows versions, the default Excel add-ins library is 'C:Program FilesMicrosoft OfficeOFFICE15Library' or 'C:Documents and Settings{username}Application DataMicrosoftAddIns'. By default in Excel 2013, Solver is located in 'C:Program FilesMicrosoft OfficeOFFICE15LibrarySOLVER'.
To set a reference to an add-in, it must first be installed. Then on the VB Editor's Tools menu, select References. This lists all open workbooks and installed add-ins, as well as a huge list of resources installed on the host computer. Find the add-in in the list, and check the box in front of its name.
With a reference set to Solver, SolverMacro2 will run as expected. In addition, the Solver library will be accessible through the VB Editor's Object Browser (right), and you will have the benefit of Intellisense (below) while editing code that uses members of the Solver library. |
Avoiding Solver Reference Problems
The code you write to run Solver will work on your computer, and on any computer with the same versions of Excel and Solver. In fact, it should work on any computer that has later versions of Excel and Solver. If you want to distribute your workbook with VBA code written for Solver, you should write the code using the earliest expected version of Excel, so it will work on all versions that users may have installed. When the workbook is first opened on a given computer, it finds the references resources, or more recent versions if available.
This sounds easy, but sometimes it isn't. Perhaps you developed a workbook in Excel 2013 for your department to use, but you have to send it to a supplier, and the supplier hasn't upgraded past Excel 2007. Or perhaps the workbook must be shared amongst a group of users who have different versions of Excel and Solver installed. In these cases, a computer with an earlier version of Solver installed will choke on the reference to a later version of Solver.
It is possible, of course, to install add-ins and set references using VBA. This can be tricky, the user has to grant permission for VBA code to access any VB projects. Without this permission, references to installed components cannot be set.
To avoid issues with installing add-ins and setting references to various resources, your code can be modified so that it is called using Application.Run. Without a reference to the add-in, you lose IntelliSense and the Object Browser, and your code suffers from a small (probably imperceptible) performance penalty. However, you gain simpler, more reliable execution. The syntax is straightforward: Application.Run is followed by the procedure name in double quotes, followed by a comma separated list of arguments being passed to the procedure:
If Application.Run is used to return the calculated result of a function, the syntax is slightly different, with a variable set equal to Application.Run, with the procedure and arguments enclosed within parentheses:
The SolverMacro2 procedure above is easily modified to use Application.Run:
A more general version of a Solver procedure is shown below. This includes more informative comments, and it provides a notice to the user about the success of the Solver optimization. Note that before Excel 2007, the name of the solver add-in was 'Solver.xla', not 'Solver.xlam'.
The results of the SolverSolve function include:
Preparing Solver for First Use
One frequent complaint about automating Solver is that it doesn't work using VBA until it has been used at least once manually. This is because Solver installs itself in a kind of 'on demand' mode. Unlike a regularly-installed add-in, it is not opened until it is first used. And until it is first used, it hasn't run its Auto_Open procedure, which is what actually prepares it to run. Using VBA you can bypass the initial manual Solver operation with this command:
This command should be run before the first Solver optimization procedure is executed. I have developed a Solver initialization routine that first makes sure the computer even has Solver, then it installs it and runs its Auto_Open procedure. The procedure is written as a function, which returns True if Solver is available and ready to use. I usually call this procedure from the parent workbook's Workbook_Open event procedure. If CheckSolver is False, I usually have the workbook close itself after a brief warning to the user.
The function above works fine for English versions of Excel, but in other languages, the name of the add-in may not be 'Solver Add-In'. We have to be a bit more clever, and introduce a loop to check the filenames of all add-ins. The CheckSolverIntl function below calls two additional functions which perform the loops. This function still relies on Solver being named 'solver.xlam'. If this is not the case, for example, in different language versions of Excel, change the value of the constant sAddIn in this procedure, and please email me about it.
Solver Links
Frontline Systems
Frontline Systems has developed Solver add-ins for Excel and other applications. The standard Excel Solver add-in can be upgraded to a premium Solver version or to other specialized Solvers, and there are versions for use with other programming platforms. While the capabilities of Solver are very extensive, the online documentation is somewhat sparse.
Frontline Systems has developed Solver add-ins for Excel and other applications. The standard Excel Solver add-in can be upgraded to a premium Solver version or to other specialized Solvers, and there are versions for use with other programming platforms. While the capabilities of Solver are very extensive, the online documentation is somewhat sparse.
Microsoft
Around the Web
In How to Get More out of Excel Solver I covered techniques to enhance your model and empower your analysis. One of the topics I received the most questions on is the automating the solver to solve for multiple values.
There was a brief explanation in the previous article, but nothing dedicated to how to implement it from start to finish. In this article we’ll go in depth from start to finish covering the concepts and methods step by step. By the end you’ll be able to automate any of your own personal models and scale your analysis.
To get the most out of this article you should be familiar with the basics of Excel Solver. If you need to brush up check out this comprehensive whitepaper, How to Use Excel Solver to Power Your Budgets.
How we’ll do it
We’ll leverage the macro recorder in Excel to handle most of the actual code. The record macro tool will allow Excel to convert our manual steps into VBA code.
We will then identify the key pieces of code to change. Then we will create a loop to dynamically select and replace these values and run the solver again.
This will be semi-technical but through this style of development we can focus on the process and the concepts instead of focusing on how to write code.
Setting up the macro recorder
The macro record functionality is found in the developer tab in the ribbon. This is not available by default but is easy to implement. If you navigate to your ribbon options you will see a checkbox for the developer tab.
The screenshot below is for Mac but PC based Excel is similar.
If you have not installed the solver add-on you can install it via the Add-ins menu. The Microsoft support site has instructions for all platforms, Load the Solver Add-in in Excel.
Recording the macro
Now that the set up is complete we we are ready to record! Set up your workspace appropriately with all your formulas and references. You’ll want to set up everything to the point before you build the solver model.
Make your way back to the developer tab on the ribbon. You will see a piece of paper with a red dot. Click on that button to open the recorder.
Give your macro a name and fill in the description if you’d like.
Once you hit Ok the recorder will begin.
Go through the steps of setting up your solver and solve it. Once this is complete go back to the developer tab and stop recording. It’ll be the same location as the button you used to start the recording.
Now that we have the base code we can clean it up and prep it for the next steps.
Clean up the code
Now we can edit the code, remove any unnecessary parts, and make sure we are only using the essential pieces of the macro recording.
You can edit the code through Excel’s VBA editor. To access your recorded code click the Macros button.
Navigate to your macro in the new menu and click edit.
Now that we have our code editor open we can start making tweaks.
Removing Unnecessary Code
If you happened to click through a bunch of other things while recording you can clear those out now and be left with something similar to below. If there is a lot of extra code and you are worried about breaking it, you can always rerecord.
Setting up the solver references
One last step before we move on, go to Tools > References and select Solver. If you don’t do this the package will not load in the macro and you’ll get an error.
You can now change your parameters open the macros menu, select your macro, hit run and it will update the results.
Let’s line up the code against our solver model. You’ll see how it lines up. The language is different but you can piece together what it all means.
Next we’ll isolate the parts we need to change and wrap up our automation.
Rinse and repeat – building the loop
Now that we have a working solver. We need to repeat that multiple times. Let’s put an example together and cement the plan.
We started with a budget of $1,000 in the first model but we want to do the same thing for $100 increments up to $2,500.
Let’s dive in! We have a list of values in column B and our output in column C. If we wrote out the process it would look like,
- Set total spend to the value in B10.
- Solve the model.
- Place the output in C10.
- Choose next B value.
- Set the output in the C row.
- Repeat steps 2-5 for all values in B.
Starting the loop
Loops are coding constructs that repeat an action until hitting a logical end point. A loop will come in handy here to repeat the same process, running the solver, on every value until we reach the end of the value.
For this example we’ll define the end point by the number of rows. B10:B25 has 16 values.
We’ll start our loop and wrap it around our solver code. We’ll set up a variable to hold the loop number (i) and go through each value, solving the loop each time.
Resetting the solver
We’ll add another more condition at the start of our code. Since we want to rerun the solver each iteration, we’ll reset it each loop. This will clear the settings and start a fresh model.
We can do this via SolverReset.
Updating cell references
We now solve sixteen times but need to update our cell references. While we originally referenced B10 we need to move down one cell each run to update the limits. We can do this via the offset function. We can update “$B$10” to reference the cell then offset by i rows (0 on the first run, 1 row on the second, and so on). Each time the loop runs, i increases by one.
Collecting the output
The output will change each time so we’ll want to save it. We can follow the same process above but offset from C10 and set it equal to the value of the solved model. Then the loop starts over.
We’ll call C10 offset it by the appropriate number of rows then paste the value of our output.
Sidestepping the solver notifications
Let’s save ourselves a headache and not learn by example for a moment. Remember when you ran the solver and that menu popped up asking if you’d like to accept the solution? That will pop up every time if you don’t disable it. Imagine if you set up a loop with 100’s of values, you’d have to click accept every time!
Go ahead and add the following lines after your solver code and add (TRUE) to SolverSolve. This will mimic you accepting the changes.
Now we have the finished code!
Up and running
We can now run the code by selecting it from the macro list and running. If you’d like to use your workbook as a template you can assign the macro to a button for ease of use. You can find these in the developer tab.
You can also add any comparison graphs. In this case we want to compare the rate of spend growth versus the conversion volume growth. This example isn’t interesting but real data can help reveal efficiency breakpoints
Conclusion
We covered quite a bit! Congratulations on getting your automated solver up and running. The example we covered was overly simplistic, but you can take the same concepts and apply it to any solver model.
This can save an enormous amount of time for models you need to solve multiple times and encourage users to experiment with different scenarios.