Learn How to Set Up and Run Solver in VBA for Excel Formula

Solver is an add-in that is provided with Excel and is used to perform ‘what-if’ analysis by providing alternative answers to a formula in a cell based on values that you may pass to the formula from other cells in your workbook. In this article, you will learn how to automate Solver in VBA in Excel formula, with a basic theory, a step-by-step procedure, and a detailed example.

Solver works by changing the values of some cells, called decision variables, to optimize the value of another cell, called the objective cell, subject to some constraints. The objective cell must contain a formula that depends on the decision variables. The constraints can be linear or nonlinear, and can specify upper or lower bounds, or equality, for the decision variables or other cells.

To use Solver in VBA, you need to enable the Solver add-in in Excel and add a reference to it in your VBA project. Then, you can use three main Solver VBA functions: SolverOK, SolverAdd, and SolverSolve. These functions correspond to the actions that you can perform interactively through the Solver Parameters, Solver Options, and Solver Results dialog boxes of the Solver add-in.

SolverOK sets up the Solver model by specifying the objective cell, the optimization method (maximize, minimize, or value of), the decision variables, and the solving method (Simplex LP, GRG Nonlinear, or Evolutionary). SolverAdd adds a constraint to the Solver model by specifying the cell reference, the relation (less than or equal to, equal to, or greater than or equal to), and the formula text. SolverSolve runs the Solver model and returns the solution status. You can also use other Solver VBA functions to modify, save, load, or reset the Solver model, or to display the Solver dialog boxes.

Procedure

The following steps show how to automate Solver in VBA in Excel formula:

1. Enable the Solver add-in in Excel by clicking the File tab, then Options, then Add-ins, then Go next to Excel Add-ins, and then checking the Solver Add-in box.
2. Add a reference to the Solver add-in in your VBA project by clicking the Tools menu in the Visual Basic Editor, then References, and then selecting Solver under Available References.
3. Write a VBA subroutine or function that uses the Solver VBA functions to set up and solve the Solver model. You can use the macro recorder to generate the code, or write it manually. For example, the following code solves a simple optimization problem:

Sub Solver_Example()
    ' Set up the Solver model
    SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
    ' Add a constraint that B2 must be equal to 0.4
    SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=0.4
    ' Solve the model and display the results
    SolverSolve True
End Sub

4. Run the VBA subroutine or function from the Visual Basic Editor, or assign it to a button or a shortcut key in the Excel worksheet.
5. Check the solution status and the values of the objective cell and the decision variables. You can also use the SolverGet function to retrieve the solution information in VBA.

Example

To illustrate how to automate Solver in VBA in Excel formula, let’s consider a scenario where you want to maximize the profit of selling two products, A and B, by choosing the optimal production quantities. The profit per unit of product A is $40, and the profit per unit of product B is $50. The production capacity is limited to 200 units in total, and the demand for product B is at least 40% of the total production. The objective is to find the production quantities of product A and B that maximize the total profit, subject to the capacity and demand constraints.

The following table shows the Excel worksheet that contains the data and the formulas for this scenario:

The objective cell is B6, which contains the formula =SUM(B4:B5). The decision variables are B1 and B2, which contain the production quantities of product A and B, respectively. The constraints are:

• B1 + B2 <= 200 (capacity constraint)
• B2 >= 0.4 * (B1 + B2) (demand constraint)

The following VBA code automates the Solver model for this scenario:

Sub Solver_Example()
    ' Set up the Solver model
    SolverOk SetCell:=Range("B6"), MaxMinVal:=1, ByChange:=Range("B1:B2")
    ' Add the capacity constraint
    SolverAdd CellRef:=Range("B1:B2"), Relation:=1, FormulaText:=200
    ' Add the demand constraint
    SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:="0.4*SUM(B1:B2)"
    ' Solve the model and display the results
    SolverSolve True
End Sub

After running the code, the Solver Results dialog box shows that the optimal solution is found, with a total profit of $9000. The optimal production quantities are 80 units of product A and 120 units of product B.

Other Approaches

Besides using Solver in VBA in Excel formula, you can also use other approaches to perform optimization problems in Excel, such as:

• Using the built-in Excel functions, such as MAX, MIN, SUMPRODUCT, or LINEST, to find the optimal values of linear problems.
• Using the Excel Data Table feature to perform sensitivity analysis by varying one or two input variables and observing the changes in the output variable.
• Using the Excel Goal Seek feature to find the value of an input variable that makes the value of an output variable equal to a desired value.
• Using the Excel Scenario Manager feature to create and compare different scenarios of input variables and output variables.