Standard Excel Solver - Controlling the Solver with VBA

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 can do this by calling a set of Solver-specific functions from a macro program you write in Visual Basic Applications Edition (VBA) for Microsoft Excel.

 

The Easiest Method of All

Controlling the Solver can be as simple as adding one line to your VBA 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 VBA module, all you need to do in your code is activate the worksheet and call the function SolverSolve in VBA.

Using the Macro Recorder

If you want 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 Macro Record New Macro...) and then set up a Solver model interactively.  Microsoft Excel will record a macro in VBA 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.

Using Microsoft Excel Help

You can learn about the Solver functions in online Help.  Just open the Visual Basic Editor (Tools Macro Visual Basic Editor...), select Help Microsoft Visual Basic Help or press F1, then type "solver" in the Answer Wizard and click Search.  This will display a list of functions, such as SolverAdd and SolverSolve, in the Topics list.  Click any of the function names for details on how the function is used.

< Back to Standard Excel Solver Support Information