VBA Solver

Excel VBA Solver

Solver is an efficient analysis tool used to automate and simulate engineering models. It can be even more effective when used in combination with VBA to simplify multiple models that use specific input parameters and limitations. You can solve equations through the VBA solver as well. I mean VBA also has Solver enabled in it which can work for you to solve such problems. In this article, we are going to see how VBA Solver can be used to solve the optimization as well as financial and simulation problems

Enabling Excel Solver Add-in

The first thing that you have to do is to enable the Excel Solver Add-in. For this, follow the below steps:

Step 1: Open a new Excel and click on the File menu.

Step 2: Go to Options.

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

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

Step 4: A new Add-ins window will pop up. Click select the Solver Add-in option in it and click OK.

Step 5: Now, you can see, Excel Solver under the Data tab within the Analysis section. See the screenshot below:

This is the first step that you need to enable solver so that you can use it under VBA.

Enable Solver under VBA

Now, we will see how to enable Solver under VBA. For this, follow the below steps:

Step 1: Open a new VBA pane through the Excel Worksheet by either hitting keyboard shortcut Alt + F11 or by navigating to Visual Basics under the Developers tab (You need to enable this option if can’t see in your Excel worksheet) through excel ribbon.

Step 2: Inside Visual Basic Editor that opens up after you hit Alt + F11 or click on Visual Basic button under the Developer tab. Navigate towards Tools and click on References.

Step 3: As soon as you click on References… under Tools, a list of VBA references will pop-up.

Step 4: Navigate towards the Solver reference and tick select it. Click OK.

Solver Function in VBA

VBA Solver has three functions namely SolverOK, SolverAdd, and SolverSolver. We need to use three of these to solve any of the equations in VBA.

This is the VBA SolverOK function with parameters:

• SetCell: Reference of the cell which needs to be changed.
• MaxMinVal: This is an optional parameter, below are numbers and specifiers.
  • 1 = Maximize
  • 2 = Minimize
  • 3 = Match a specific value
• ValueOf: Should be used when MaxMinVal has value 3.
• ByChange: Cells which needs to be changed to solve the equation.

VBA SolverAdd Function

This is VBA SolverAdd function which has the following arguments:

1. CellRef: The cell reference works as criteria to solve the equation by changing the value.
2. Relation: is an integer value between 1 to 6 which specifies the logical relation as follows:

• 1 for less than or equals to (<=).
• 2 for equals to (=).
• 3 for greater than or equals to (>=).
• 4 for all integers.
• 5 for fraction between o and 1.
• 6 for all different values which are integers.

Example of Solver in Excel VBA

We will learn how to use Solver using the VBA code in Excel.

We wanted to get a minimum profit of 12000 by changing the values of Item to be Sold (which should be an integer) and Unit Price (which should be between 8 to 12).

Let’s do this in VBA Solver. For this, follow the below steps:

Step 1: Define a new sub-procedure.

Code:

Sub Example()

End Sub

Step 2: Use SolverOK so that we can set the objective cells associated with the given problem.

Code:

Sub Example()

SolverOk (

End Sub

Step 3: Since we need to set the profit value, use B8 as a SetCell argument under the SolverOK function.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"),

End Sub

Step 4: We are expecting a specific profit value (12000).  Therefore, set the MaxMinVal as 3 and ValueOf as 12000 inside SolverOK.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000,

End Sub

Step 5: For ByChange, we need to provide the range of cells whose values should be changed to solve this optimization problem. In this case, we will be using Item to be Sold as well as Unit Cost values (B1 and B2 respectively) as a reference.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")

End Sub

Rest other arguments are optional and it is ok if you don’t put those in this function.

Step 6: Now, we will add the constraints under for the Solver using SolverAdd function. Initiate SolverAdd function under VBA.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd(

End Sub

Step 7: The First argument for SolverAdd is CellRef. For us, we need to change the Unit Cost for the product to get the equation solved. Therefore, we will add B2 as the first argument under SolverAdd.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"),

End Sub

Step 8: Second Argument for Relation should have a value 3. Since we wanted the Unit Cost to be more than 8 (>=8). Set it inside the function.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3,

End Sub

Step 9: FormulaText should contain the value which you wanted to set for the Unit Cost. In this case, it would be 8.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, Formulatext:=8

End Sub

Step 10: Use the same SolverAdd function. But this time to set the minimum bound as 12 for this constraint.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, Formulatext:=8
SolverAdd CellRef:=Range("B2"), Relation:=1, Formulatext:=12

End Sub

Step 11: Add one more SolverAdd function so that we can set the value for items to be sold as Integer.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, Formulatext:=8
SolverAdd CellRef:=Range("B2"), Relation:=1, Formulatext:=12
SolverAdd CellRef:=Range("B2"), Relation:=4, Formulatext:="Integer"

End Sub

Step 12: The final thing for this code is to add SolverSolve.

Code:

Sub Example()

SolverOk Setcell:=Range("B8"), MaxMinVal:=3, valueof:=12000, bychange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, Formulatext:=8
SolverAdd CellRef:=Range("B2"), Relation:=1, Formulatext:=12
SolverAdd CellRef:=Range("B2"), Relation:=4, Formulatext:="Integer"
SolverSolve

End Sub

Step 13: Now, run this Code and you’ll see a solver window popping up on your excel. Click on the OK button under this Solver window.

Step 14: You will see an output as shown below:

What this means? In order to achieve a profit of 12000 for a product which has Unit Cost (Purchase Cost) of 6.00, you need to sell 6000 items of that product with Unit Price as 7.00.

Let’s wrap the things up with some points to be remembered:

Things to Remember

• Before using Solver under VBA, it is Mandatory to enable the same under Excel Workbook. The procedure is shared about how to enable the same under both Excel as well as VBA.
• It is also mandatory to enable Solver Under VBA and then only you can use the VBA Solver to solve the equations.
• There are three different functions which can be used to solve an equation under VBA. Namely, SolverOK, SolverAdd, and SolverSolve. Without these three, you can’t solve any equation under VBA.

Recommended Articles

This is a guide to VBA Solver. Here we discuss how to enable and use solver in excel VBA with the help of practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA XLUP
2. VBA Resize
3. VBA Replace String
4. VBA Login