VBA Solver

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Solver

How do you solve complicated problems? If you are unsure how to go about these problems, then there is nothing to worry about; we have a solver in Excel. In our earlier article, “Excel Solver,” we learned how to solve equations in Excel. If you are unaware, “SOLVER” is also available with VBA. This article 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 excelSOLVER In ExcelThe solver in excel is an analysis tool that helps find solutions to complex business problems requiring crucial decisions to be made. For every problem, the goal (objective), variables, and constraints are identified. The solver returns an optimal solution which sets accurate values of the variables, satisfies all constraints, and meets the goal. read more, 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 the “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 “Solver Add-in” box. Click on “OK.”

Enable Solver in excel 1.4

Next, you must see “Solver” under the “Data” tab.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Enable Solver in VBA

In VBA, Solver is an external tool. So, 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 codeWrite A VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, we need to use three “Solver Functions” in VBA: “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., the “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 us see the parameters of SolverAdd.

SolverAdd

CellRef: To set the criteria to solve the problem, what cell needs to be changed?

Relation: If the logical values are satisfied, we can use the numbers below.

  • 1 is less than (<=)
  • 2 is equal to (=)
  • 3 is greater than (>=)
  • 4 must-have final values that are integers.
  • 5 must-have values between 0 or 1.
  • 6 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

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 10,000. 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 must identify how many units to sell. At what price to get the profit value of 10,000?

Let us solve this equation now.

Step 1: Start the VBA sub procedureVBA SubprocedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

Code:

Sub Solver_Example()
End Sub
VBA Solver Example 1.1

Step 2: First, we need to set the Objective cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more using the SolverOk function.

VBA Solver Example 1.2

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

Code:

Sub Solver_Example()

    SolverOk SetCell:=Range("B8")

End Sub
VBA Solver Example 1.3

Step 4: We need to set this cell value to 10,000. 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 10,000.

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, changing Units to Sell (B1) and Price Per Unit (B2) cells need 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 we set the objective cell, we must construct other criteria. So, for this, open the “SolverAdd” function.

VBA Solver Example 1.6

Step 7: First Cell Ref we need to change is Price Per Unit cell, 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, 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: The first cell,  “Units to Sell,” must be an Integer value. So, for this, also set up the criteria 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

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.” You will get the result in an Excel sheet.

Solver Result 1

So, to earn a profit of 10,000, we need to sell 5,000 units at 7 per price where the cost price is 5.

Things to Remember

  • To work with Solver in Excel and VBA, first, enable it for the worksheet, then enable it for VBA reference.
  • We once enabled it on worksheets and VBA; we can only access all the Solver functions.

This article 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: –