You can control Solver from VBA, defining and solving problems just as you do interactively. Using Solver VBA functions, you can display or completely hide the Solver dialog boxes, create or modify the choices of objective cell, constraints and decision variable cells, check whether an optimal solution was found, and produce reports.
Running Predefined Solver Models
Controlling Solver can be as simple as adding one line to your VBA 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, you can simply add a reference to the Solver add-in (see below), activate the worksheet, and add one line to call the function SolverSolve in VBA.
Using the Macro Recorder
If you want to set up a Solver model “from scratch” programmatically, one easy way to see how to use the Solver VBA functions is to turn on the Excel Macro Recorder (click Record Macro on the Developer tab), and then set up a Solver model interactively. Microsoft Excel will record a macro in VBA that 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 VBA Help
The Solver VBA functions are documented in Help for Excel VBA. Open the Visual Basic Editor (Alt-F11), select Help - Microsoft Visual Basic Help, and type ‘Solver’ in the Search box to display a list of function names.
Referencing Functions in Visual Basic
To use the Solver VBA functions, your Visual Basic module must include a reference to the Solver add-in (Solver.xlam). In Microsoft Excel, click Solver in the Analysis group on the Data tab to ensure that the Solver add-in is loaded. Then (after closing the Solver Parameters dialog) press Alt-F11 to open the Visual Basic Editor, choose Tools References... and make sure that the box next to Solver is checked.
Checking Function Return Values
Solver VBA functions generally return integer values, which you should check in your VBA code. The normal return value is 0, indicating that the function succeeded. Other possible return values are given in the descriptions of the individual functions. If the arguments you supply are invalid, an error condition can be raised, which you would have to handle via an On Error VBA statement.
Of particular interest is the return value of the SolverSolve function, which describes the result of the solution process. The return values can range from 0 to 20, and correspond one-for-one with Solver Result Messages that appear when Solver stops and displays the Solver Results dialog box.
One group of functions can return a variety of numeric, logical, string or array values, depending on the arguments you supply. These functions (SolverGet, SolverOkGet, etc.) may be used to “read” the settings of the current Solver model, on the active sheet or any other worksheet whose name you supply.