Controlling the Solver with Macros and VBA

[Dividing Line Image]

You can control every aspect of the Solver's operation programmatically. You can display or completely hide the Solver dialog boxes, create or modify the choices of objective (target cell), variables (changing cells) and constraints, check whether an optimal solution was found and produce reports. You do this by calling a set of Solver-specific functions from a macro program you write either in the Excel 4.0 macro language, or in Visual Basic Applications Edition (VBA) for Microsoft Excel. (For most new projects, you would choose VBA.)

The Easiest Method of All

Controlling the Solver can be as simple as adding one line to your macro program code! Each worksheet in a workbook may have a Solver problem defined, which is saved automatically with the workbook. You can create this Solver model interactively if you wish. If you distribute such a workbook, with a worksheet containing a Solver model and a macro sheet or VBA module sheet, all you need to do in your code is activate the worksheet and call the function SolverSolve in VBA, or SOLVER.SOLVE() in the macro language.

Using the VBA Object Browser

If you are writing in VBA, you can obtain help with the Solver function names and arguments by clicking on the Object Browser icon in the VBA icon palette. Select SOLVER.XLA from the Libraries/Workbooks dropdown list to bring up a list of Solver functions. You can select from this list, view the function arguments, and paste a prototype function call into your VBA module.

Using the Function Wizard

If you are writing in the macro language, you can obtain help with the Solver function names and arguments by clicking on the Function Wizard icon (labeled fx) on the main Excel toolbar. Select All from Function Category, and scroll the list down to the Solver functions (all of which start with SOLVER). You can choose a function, view the arguments, and paste a prototype function call into your macro language code.

Using Microsoft Excel Help

You can learn about the Solver functions in online Help. Although either the VBA or the macro language functions may be used in either Excel 5.0 or Excel 7.0, you'll find Help only on the VBA functions in Excel 7.0, and Help only on the macro language functions in Excel 5.0.

In Excel 7.0, choose the Contents tab from the Help dialog, then select Microsoft Excel Visual Basic Reference, then Functions, and finally the letter S for Help on the VBA functions. In Excel 5.0, choose Help Contents, Reference Information, Microsoft Excel Macro Functions Contents, Macro Functions Listed by Category, and finally Command-Equivalent.

Using the Macro Recorder

If you want or need to set up the Solver model "from scratch" programmatically, one easy way to see how to use the Solver functions is to turn on the Macro Recorder (Tools Record Macro...) and then set up a Solver model interactively. Microsoft Excel will record a macro, either in VBA or in the macro language, which calls the Solver functions to mimic the actions you perform. You can then edit and customize this macro, and incorporate it into your application.

In neither Excel 5.0 nor Excel 7.0 will the Macro Recorder record the "native" VBA Solver function calls; instead it uses the VBA method Application.ExecuteExcel4Macro with the equivalent macro language function call as a String argument. We'd expect native VBA functions to be recorded future versions, but all such decisions are up to Microsoft.

VBA and Macro Function Reference (24K)

As a convenience for Microsoft Excel 5.0 users who are creating macro programs to control the Solver in VBA, and who have only the Object Browser information to work with, we've included our own description of the VBA and macro language function calls in a separate Web page. To display this page, click on the heading above. We recommend that you save or print this page for future reference.