Solver must know the target cell, the adjustable cells, the ranges with constraints, and you can also give Solver orders about solution method, solution precision etc. When you call Solver using a macro, you need to do several things, just like when you use it manually. If it doesn't work using Solver manually, it will not work with VBA. I strongly recommend that you make a model and test it by using Solver manually, before you use VBA. Not surprisingly Solver is normally used for more complex stuff, and when it starts smelling of equations with several unknowns, it pays off to make Solver your friend.īeware of If functions in cells - if there are too many nested IFs, Solver may suffer from indigestion. You don't need to have a target cell - you could also make "A3 = 4" a constraint. In the Danish version of Excel 2003 it looks like this:Ĭlick "Solve", and Solver inserts 2 in cell A1. Make A3 your target cell, the value must be 4 by adjusting cell A1. Type "=A1+A2" in cell A3 and start Solver from the menu. Select "solver.xla", or "solver.xlam" if you use Excel 2007 or newer.īefore you use Solver, you must make a model with one or more target or objective cells and one or more adjustable cells, that will influence the values of the target cells. If Solver is not on the list, you have to click "Browse" and find the folder "Solver" buried as a subdirectory to "Microsoft Office" in the Programs folder.Ĭlick on "File types" and select Excel files (xls + xla or xlsm + xlam). The VBA reference is set from "References" in the VBA editor's Tools menu. To use Solver it must be referenced as an AddIn in Excel's "spreadsheet part," and if you want to use it with VBA, it must also be referenced here.
Solver is not installed automatically, when you install Excel - it is an option. The user controls the application with a right-click popup menu. To ensure that TextBoxes get numeric values only, there is a modified version of the class module described on this page.
See below: Installing Solver.īesides macros to control Solver there are also examples on how to use Ranges, Arrays and UserForms with ListBoxes, ComboBoxes, TextBoxes, OptionButtons and CommandButtons. The workbook was made in Excel 2003, so you probably need to change the references to Solver. To unzip, right-click and select "decompress all" or whatever Windows suggests.
You can download a much simpler version here. how much to use of different raw materials and fuels to get the desired quality. I work at a cement plant and have made an application for raw mix proportioning, i.e. An application can also be used by others that don't know much about Solver. Solver has many uses, and if it is a recurrent task, it can be a big advantage to make an application with VBA macros. How to mix raw materials to get a certain chemical composition at the lowest cost? How much ice cream to produce as a function of the weather forecast and day of the week? What buttons to press (how hard) to maximize profit? This page shows how to control Excel's Solver using VBA macros, and you can download a sample VBA application, that uses Solver.Įxcel's Solver is a strong tool to calculate and/or optimize complex models.