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.

VBA Goal Seek - Definition Example - 1

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.

VBA Goal Seek - Definition Example - 2

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 - Definition Example - 3

Key Takeaways
  • To attain desired outcomes in target cells, VBA Goal Seek assists in determining input values.
  • VBA Goal Seek with Constraints deals with using VBA to apply conditions or constraints to the Goal Seek operation. Limitations can narrow the scope of feasible solutions, forcing Goal Seek to identify solutions that satisfy particular requirements or conform to certain restrictions.
  • You can configure your VBA code in Goal Seek with Variable Range to dynamically define a range of cells as changing variables in addition to adjusting a single variable.
  • Take into consideration employing loops for multiple changing cells and be mindful of iteration limits and convergence issues.

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)

To use VBA Goal Seek in Excel, follow these steps:

  1. Open Excel and press ALT + F11 to open the VBA editor.

    How to use Goal Seek in Excel VBA - Step 1

  2. Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

    How to use Goal Seek in Excel VBA - Step 2

  3. Inside the module, write your code. The basic syntax for Goal Seek in VBA is as follows:

    Range("TargetCell").GoalSeek Goal:=TargetValue, ChangingCell:=ChangingCell

  4. You can use the VBA editor’s Run → Run Sub/UserForm or the F5 key to run your code.

  5. View the results in your worksheet after the VBA code has completed running.

    Now that you’ve established your goal, the value in the target cell should match it, and the changing cell or cells should have been adjusted appropriately.


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.

VBA Goal Seek in Excel - Example 1
  • 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.
Example 1 - Step 1
  • 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.
 Example 1 - Step 2
  • Step 3: Now, we define a VBA subroutine named “GoalSeek1”. This subroutine will perform the Goal Seek operation.
Example 1 - Step 3
  • 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.
Example 1 - Step 4
  • 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.

VBA Goal Seek in Excel - Example 1 - Step 5
  • 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.

VBA Goal Seek in Excel - Example 1 - Step 6

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.

Excel VBA Goal Seek - Example 2
  • 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.
 Example 2 - Step 1
  • Step 2: Here, we declare two Range variables, TargetCell and ChangingCell, which will be used to specify the cells in our worksheet.
Example 2 - Step 2
  • 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.
Example 2 - Step 3
  • 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.
Example 2 - Step 4
  • 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.
Example 2 - Step 5
  • 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.

Excel VBA Goal Seek - Example 2 - Step 6
  • 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.

Excel VBA Goal Seek - Example 2 - Step 7

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)

1. Can I use VBA Goal Seek with a specific macro or script?

Yes, VBA Goal Seek can be utilized inside of a particular macro or script. You can automate the process of determining input values in your Excel worksheets that accomplish particular goals by writing VBA code that uses Goal Seek. This enables you to incorporate Goal Seek features into your own scripts and macros.

2. Is it possible to undo VBA Goal Seek changes?

No, you cannot use an Excel undo operation to directly reverse VBA Goal Seek changes. The modifications to the changing cell(s) are applied to accomplish the designated goal after you execute a Goal Seek operation in VBA. You would have to manually record or save the original values before launching Goal Seek, and then restore them if necessary, in order to go back to the original values.

3. Are there any limitations to VBA Goal Seek?

Yes, there are some limitations:
VBA Goal Seek may not always converge to a solution, depending on the problem’s complexity and the initial conditions.
To achieve a goal in a different cell (the target cell), VBA Goal Seek modifies a single cell (the changing cell). Multi-dimensional problems with multiple variables changing at the same time might be beyond its capabilities.
When working with very large or very small numbers, or in scenarios involving complex formulas, VBA Goal Seek might not be able to find exact solutions.

4. Can VBA Goal Seek handle multiple changing cells?

Excel’s built-in Goal Seek feature and the VBA Goal Seek method both deal with a single changing cell by default. Nevertheless, by running Goal Seek repeatedly for every changing cell or by incorporating more sophisticated optimization strategies into your script, you can write unique VBA code to manage numerous changing cells. This enables you to work with situations where a number of variables must be changed in order to achieve a particular objective.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *