## Excel VBA Solver

How do you solve complicated problems? If you are not sure how to go about these problems then nothing to worry we have solver in our excel. In our earlier article “Excel Solver” we have learned how to solve equations in excel. If you are not aware “SOLVER” is available with VBA as well. In this article, we will take you through how to use “Solver” in VBA.

### Enable Solver in Worksheet

A solver is a hidden tool available under the data tab in excel (if already enabled).

To use SOLVER in excel first we need to enable this option. Follow the below steps.

**Step 1: **Go to the FILE tab. Under the FILE tab choose “Options”.

**Step 2: **In Excel Options window choose “Add-Ins”.

**Step 3: **At the bottom choose “Excel Add-Ins” and click on “Go”.

**Step 4: **Now check the box “Solver Add-in” and click on Ok.

Now you must see “Solver” under the data tab.

### Enable Solver in VBA

In VBA too, Solver is an external tool, we need to enable it to use it. Follow the below steps to enable it.

**Step 1: **Go to Tools >>> Reference in Visual Basic Editor Window.

**Step 2: **From the references list, choose “Solver” and click on Ok to use it.

Now we can use Solver in VBA as well.

### Solver Functions in VBA

To write a VBA code we need to use three “Solver Functions” in VBA and those functions are “SolverOk, SolverAdd, and SolverSolve”.

#### SolverOk

**SolverOk ( SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)**

**SetCell:** This will be the cell reference that needs to be changed i.e. Profit cell.

**MaxMinVal: **This is an optional parameter, below are numbers and specifiers.

- 1 = Maximize
- 2 = Minimize
- 3 = Match a specific value

**ValueOf: **This parameter needs to supply if the **MaxMinVal **argument is 3.

**ByChange: **By changing which cells this equation needs to be solved.

#### SolverAdd

Now let’s see the parameters of **SolverAdd**

**CellRef:** To set the criteria to solve the problem what is the cell needs to be changed.

**Relation: **In this, if the logical values are satisfied then we can use the below numbers.

- 1 is less than (<=)
- 2 is equal to (=)
- 3 is greater than (>=)
- 4 is must have final values that are integers.
- 5 is must have values between 0 or 1.
- 6 is must have final values that are all different and integers.

### Example of Solver in Excel VBA

For an example look at the below scenario.

Using this table we need to identify the “Profit” amount which needs to be a minimum of 10000. To arrive at this number we have certain conditions.

**Units to Sell**should be an integer value.**Price / Unit**should be between 7 and 15.

Based on these conditions we need to identify how many units to sell at what price to get the profit value of 10000.

Ok, let’s solve this equation now.

**Step 1: **Start the VBA subprocedure.

**Code:**

Sub Solver_Example() End Sub

**Step 2: **First we need to set the Objective cell reference by using the **SolverOk **function.

**Step 3: **First argument of this function is “SetCell”, in this example we need to change the value of Profit cell i.e. B8 cell.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8") End Sub

**Step 4: **Now we need to set this cell value to 10000, so for **MaxMinVal **use 3 as the argument value.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3 End Sub

**Step 5: **The next argument **ValueOf **value should be 10000.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000 End Sub

The next argument is ByChange i.e. by changing which cells this equation needs to be solved. In this case by changing Units to Sell (B1) and Price Per Unit (B2) cell needs to be changed.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") End Sub

Note: remaining arguments are not required here.

**Step 6: **Once the objective cell is set, now we need to construct other criteria’s. For this open “SolverAdd” function.

**Step 7: **First **Cell Ref **we need to change is Price Per Unit cell i.e. B2 cell.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2") End Sub

**Step 8: **This cell needs to be >= 7, so the **Relation **argument will be 3.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3 End Sub

**Step 9: **This cell value should be >=7 i.e. **Formula Text = 7**.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 End Sub

**Step 10: **Similarly the same cell needs to be less than 15, so for this **relation **is <= i.e. 1 as the argument value.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 End Sub

**Step 11: **First cell i.e. Units to Sell must be an Integer value for this also set up the criteria as below.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" End Sub

**Step 12: **One final step we need to add the SolverSolve function.

**Code:**

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" SolverSolve End Sub

Ok, run the code by pressing the F5 key to get the result.

When you run the code you will see the following window.

Press Ok and you will get the result in excel sheet.

So in order to earn a profit of 10000, we need to sell 5000 units at 7 per price where the cost price is 5.

### Things to Remember

- To work with Solver in excel & VBA, first, enable it for worksheet, then enable for VBA reference.
- Once it is enabled on both worksheet and VBA then only we can access all the Solver functions.

### Recommended Articles

This has been a guide to VBA Solver. Here we discuss how to enable and use solver in excel VBA with the help of an example and downloadable excel sheet. You can learn more from the following articles –

- Resize in Excel VBA
- LOOKUP Function in VBA
- Use CDate Function in Excel VBA
- Excel for Finance Course
- Goal Seek in Excel
- Scenario Manager in Excel
- Sensitivity Analysis in Excel