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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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.

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

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 = Maximize2 = Minimize3 = Match a specific value

ValueOf: This parameter needs to supply if the MaxMinVal argument is 3.

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.

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.

Sub Solver_Example()

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

End Sub

Step 5: The next argument ValueOf value should be 10,000.

Sub Solver_Example()

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

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.

Step 8: This cell needs to be >= 7, so the Relation argument will be 3.

Step 9: This cell value should be >=7, Formula Text = 7.

Step 10: Similarly, the same cell needs to be less than 15, so for this relation is <= i.e. 1 as the argument value.

Step 11: The first cell,  “Units to Sell,” must be an Integer value. So, for this, also set up the criteria below.

Step 12: In one final step, we need to add the SolverSolve function.

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.

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

  • VBA LOOKUPScenario Manager in ExcelSensitivity Analysis in Excel