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

##### Table of contents

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

**Step 2: **In the “Excel Options” window, choose “Add-ins.”

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

**Step 4: **Now, check the “Solver Add-in” box. Click on “OK.”

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

**VBA Basics Course (16+ Hours of Video Tutorials**)

**–>>** **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.

**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 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 ( 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**.

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

Look at the below scenario.

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

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

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

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

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

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

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.

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

**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, F**ormula 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: **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

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

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

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.

### Recommended Articles

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