EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA File & Directories Functions VBA Solver
Secondary Sidebar
VBA File & Directories Functions
  • VBA File & Directories
    • VBA Get Cell Value
    • VBA FileDialog
    • VBA Delete File
    • VBA Check File Exists
    • VBA DIR
    • VBA GetOpenFileName
    • VBA Write Text File
    • FileCopy in VBA
    • VBA FreeFile
    • VBA Solver

VBA Solver

By Madhuri ThakurMadhuri Thakur

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

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (85,982 ratings)

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Open File menu

Step 2: Go to Options.

Options

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

VBA Solver Example 2

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

VBA Solver Example 3

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

VBA Solver Example 4

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

VBA Solver Example 5

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.

Developer Tab

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.

VBA Solver Example 2

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

VBA Solver Example 3

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

VBA Solver Example 4

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.

Ok Function Syntax

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.

Add Function Syntax

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.

You can download this VBA Solver Excel Template here – VBA Solver Excel Template

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

VBA Solver Example

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

VBA Solver Example 1-1

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

Code:

Sub Example()

SolverOk (

End Sub

VBA Solver Example 1-14

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

OK function Example 1-2

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

MaxMinVal Example 1-4

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

VBA Solver Example 1-5

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

VBA Solver Example 1-6

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

VBA Solver Example 1-7

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

Relation Example 1-8

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

Example 1-9 (FormulaText)

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

VBA Solver Example 1-10

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

Example 1-11 (Add function)

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

VBA Solver Example 1-16

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.

VBA Solver Example 1-13 (solver window)

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

VBA Solver Example 1-17

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
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more