# VBA Solver Article byJeevan A Y ## 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.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Solver (wallstreetmojo.com)

### 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 chooses “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

You can download this VBA Solver Excel Template here – VBA Solver Excel Template

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: In 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 an 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 worksheets and VBA then only we can access all the Solver functions.

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

• 35+ Courses
• 120+ Hours
• Full Lifetime Access
• Certificate of Completion
LEARN MORE >>