![]() ![]() ![]() Solver has many uses, and if it is a recurrent task, it can be a big advantage to make an application with VBA macros. An application can also be used by others that don't know much about Solver. I work at a cement plant and have made an application for raw mix proportioning, i.e. how much to use of different raw materials and fuels to get the desired quality. You can download a much simpler version here. To unzip, right-click and select "decompress all" or whatever Windows suggests. The workbook was made in Excel 2003, so you probably need to change the references to Solver. 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. To ensure that TextBoxes get numeric values only, there is a modified version of the class module described on this page. The user controls the application with a right-click popup menu. Solver is not installed automatically, when you install Excel - it is an option. 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. The VBA reference is set from "References" in the VBA editor's Tools menu. 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). 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. Type "=A1+A2" in cell A3 and start Solver from the menu. Make A3 your target cell, the value must be 4 by adjusting cell A1. In the Danish version of Excel 2003 it looks like this:Ĭlick "Solve", and Solver inserts 2 in cell A1. You don't need to have a target cell - you could also make "A3 = 4" a constraint. 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. I strongly recommend that you make a model and test it by using Solver manually, before you use VBA. If it doesn't work using Solver manually, it will not work with VBA. When you call Solver using a macro, you need to do several things, just like when you use it manually. 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.
0 Comments
Leave a Reply. |