VBA Goal Seek

What is Goal Seek in Excel VBA?

VBA Goal Seek function is a strong tool that lets you determine the input value required in a cell to produce a desired outcome. It’s especially helpful when you want to find the matching input value that will enable you to reach a target value that you have in mind.

Let us look at an example. Let’s say you have a simple loan calculation and you want to determine the interest rate required to pay back a $10,000 loan with $200 monthly payments over the course of five years. First, you can use the following formula to get the monthly payment for a $10,000 loan with a 10% annual interest rate over five years and monthly payments:

= PMT (A1, 5 * 12, -10000)

When you plug in the values:

• A1 contains an annual interest rate of 10%.
• 5*12 represents the total number of payment periods (60 months for 5 years).
• -10000 is the loan amount.

The monthly payment will be calculated by Excel, and the outcome will be -$212.47.

We will now use the VBA Goal Seek to calculate the percentage of $200 that we can pay each month to pay off $10,000.

The formula to determine the monthly payment based on the interest rate is shown in this example in B1. By executing this code, cell A1’s interest rate will be adjusted to produce a $200 monthly payment.

Upon running the VBA code, you will observe that our condition is satisfied by changing Cell A1 to 7%.

VBA Goal Seek Syntax

The syntax for using Goal Seek in VBA is as follows:

Range(“TargetCell”).GoalSeek Goal:=TargetValue, ChangingCell:=ChangingCellRange

Here is the breakdown of the syntax:

• Range(“TargetCell”): Replace this with the reference to the cell containing the formula or value you want to set a target for.
• .GoalSeek: This is the method used to invoke the Goal Seek operation on the specified range.
• Goal:=TargetValue: Replace “TargetValue” with the desired value you want to achieve in the target cell.
• ChangingCell:=ChangingCellRange: Replace “ChangingCellRange” with the reference to the cell or range of cells that you want to adjust to reach the goal.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use Goal Seek in Excel VBA? (with Steps)

Examples

Example #1

Let’s say you have a cost model and you want to determine the best price per unit to maximize your profit. The VBA goal seek function can be used to accomplish this, as it can automatically determine the best price per unit to maximize profit.

• Step 1: First, we create a custom VBA function called MaximizeProfit in the new module. Based on the quantity of units sold, this function will determine the profit and return a double data type as the result.

• Step 2: Here, we compute the profit. Assuming, for example, that profit is ten times the quantity of units, it multiplies the value in cell B2, which represents the number of units, by 10. The result is assigned to the MaximizeProfit variable.

• Step 3: Now, we define a VBA subroutine named “GoalSeek1”. This subroutine will perform the Goal Seek operation.

• Step 4: In this part, we specify the Goal Seek operation:
  • Range(“C2”) is the target cell where we want to maximize profit.
  • Goal:=MaximizeProfit specifies that we want to use the custom function MaximizeProfit as the goal to maximize.
  • ChangingCell:=Range(“B2”) indicates that we want to change the value in cell B2 (number of units) to achieve the goal.

• Step 5: Now, save the module and exit the VBE.

In the Excel worksheet where you want to run this code, press alt+F8 to open the Macro window.

In the Macro window, select GoalSeek1 and click on run.

• Step 6: When this code is executed, it uses the VBA Goal to determine the profit (in cell C2) as 25 times the quantity of units (in cell A2) given in your worksheet. Determine how many units must be sold in order to maximize profit.

In this case, we would need to increase the price per unit to 25, to get a maximum profit of 625.

Here is the full code:

Function MaximizeProfit() As Double
MaximizeProfit = Range(“B2”).Value * 25 ‘ Example: Profit is 25 times the number of units
End Function

Sub GoalSeek1()
Range(“C2”).GoalSeek Goal:=MaximizeProfit, ChangingCell:=Range(“A2”)
End Sub

Example #2

In this example, we will understand how to find the break-even point using the VBA Goal Seek function. To do this, we will automatically adjust the number of units sold (in cell B1) until the profit/loss (in cell B3) equals zero, which represents a situation where total revenue and total cost are equal.

• Step 1: We first define a VBA subroutine called “FindBreakEven” in the new module. The Goal Seek function will be used by this subroutine to determine the break-even point.

• Step 2: Here, we declare two Range variables, TargetCell and ChangingCell, which will be used to specify the cells in our worksheet.

• Step 3: In this line, we set the “TargetCell” to be cell B3. We compute the profit or loss in Cell B3, and our goal is to determine the point at which this value equals zero or the break-even point.

• Step 4: Here, we select cell B1 as the “ChangingCell”. The input for the number of units sold is represented by cell B1, and the Goal Seek operation will modify this value in order to attain a profit or loss of zero.

• Step 5: The VBA Goal Seek operation is carried out here. In order to determine the break-even point, it modifies the number of units sold in cell B1 (as indicated by ChangingCell) until the profit or loss in cell B3 (as indicated by TargetCell) equals zero.

• Step 6: Now, save the macro and exit the VBE.

In the Excel worksheet where you want to run this code, press alt+F8 to open the Macro window.

From the VBA macro window, select “FindBreakEven” and click on run.

• Step 7: It determines the break-even point by utilizing the VBA Goal Seek function. This involves varying the quantity of units sold (in cell B1) until the profit or loss (in cell B3) is zero. In other words, it finds the point at which total revenue and total cost equal one another, and there is no profit or loss.

So, in our case, Cell B1 is changed to $1,200 indicating that at this price, we will be in a breakeven position.

Here is the full code:

Sub FindBreakEven()
Dim TargetCell As Range
Dim ChangingCell As Range
Set TargetCell = Range(“B3”)
Set ChangingCell = Range(“B1”)
TargetCell.GoalSeek Goal:=0, ChangingCell:=ChangingCell
End Sub

Important Things to Note

• When using VBA Goal Seek, you can set limits on the number of iterations to control the optimization process. In order to avoid infinite looping and guarantee that the Goal Seek operation ends after reaching the specified limit, the VBA Goal Seek Limit Iterations require setting a maximum number of iterations.
• VBA Goal Seek might not always be able to solve an issue, particularly with intricate models. Because it uses iterative computations, it might not always converge to a conclusion.
• A VBA Goal Seek Loop describes a condition in which the Goal Seek procedure is repeatedly automated and repeated using a loop structure.
• VBA Goal Seek typically handles one changing cell, but you can use loops and additional VBA logic to handle multiple changing cells.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Goal Seek, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Goal Seek. Here we learn the Goal Seek syntax & how to use it in Excel VBA code, along with examples & points to remember. You can learn more from the following articles –

• Goal Seek In Excel
• VBA Solver
• What-If Analysis in Excel