VBA Solver

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.

VBA-Solver

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”.

Enable Solver in excel 1.1

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

Enable Solver in excel 1.2

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

Enable Solver in excel 1.3

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

Enable Solver in excel 1.4

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.

Enable Solver in VBA 1

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

Enable Solver in VBA 1.1

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

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

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.

VBA Solver Example 1

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
VBA Solver Example 1.1

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

VBA Solver Example 1.2

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
VBA Solver Example 1.3

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
VBA Solver Example 1.4

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

Code:

Sub Solver_Example()

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

End Sub
VBA Solver Example 1.5

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
VBA Solver Example 1.5.1

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.

VBA Solver Example 1.6

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
VBA Solver Example 1.7

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
Example 1.8

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
Example 1.9

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
Example 1.10

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
Example 1.11

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
Example 1.12

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

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

Solver Result

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

Solver Result 1

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 >>