A Goal Seek Overkill

Posted by Parajuli Sushil 11 months Ago Add Comment 8 Min Read

What a Excel Function Can't Do

Almost everyone who starts creating custom worksheet functions using VBA makes a fatal mistake: they try to get the function to do more than is possible.

A worksheet function returns a value, and the function must be completely “passive.” In other words, the function can’t change anything on the worksheet. For example, you can’t develop a worksheet function that changes the formatting of a cell. (Every VBA programmer has tried, and not one of them has been successful!) If your function attempts to perform an action that isn’t allowed, the function simply returns an error.

Nevertheless, the preceding paragraph isn’t absolutely true. There are a few cases in which a VBA function used in a formula can have an effect. For example, it’s possible to create a custom worksheet function that adds or deletes cell comments. For the most part, however, functions used in formulas must be passive. 

VBA functions that aren’t used in worksheet formulas can do anything that a regular Sub procedure can do, including changing cell formatting.

Excel Formula v/s Excel Sub-Procedure

While Excel formulas are powerful tools for performing calculations and manipulating data within a cell, there are certain tasks that can be accomplished with VBA procedures but not with Excel formulas. Here are some examples:

  1. Automated Data Import/Export: VBA can be used to automate the process of importing and exporting data from external sources, such as databases or text files. This goes beyond the capabilities of Excel formulas.
  2. User Form Creation: VBA allows you to create custom user forms with input fields, buttons, and other controls. This is not possible with Excel formulas, which are confined to cells.
  3. Interacting with External APIs: VBA procedures can be used to interact with external APIs (Application Programming Interfaces), allowing you to fetch data from web services or perform other actions that require communication with external systems.
  4. File Manipulation: VBA can be used to create, delete, move, or copy files on the file system. Excel formulas don’t have this level of file manipulation capability.
  5. Event Handling: VBA procedures can respond to specific events in Excel, such as workbook open/close events, worksheet change events, etc. This allows for the automation of tasks based on user actions or changes in the spreadsheet.

In summary, VBA procedures extend Excel’s capabilities by allowing you to automate complex tasks, interact with external systems, and create more dynamic and customized solutions compared to what can be achieved with Excel formulas alone.

What is a Goal Seek Tool in Excel?

Goal Seek is particularly handy for financial modeling, scenario analysis, and sensitivity testing, where you need to understand how changes in one variable impact the overall outcome.

It’s particularly useful when you have a specific target in mind and want to determine the input required to reach that goal. Here’s a brief explanation of how Goal Seek works:

  1. Objective: Identify a cell containing a formula that produces a result you want to achieve. This cell is often referred to as the “Set Cell.”
  2. Target Value: Specify the value you want the Set Cell to reach.
  3. Adjustable Cell: Choose the input cell (also known as the “By Changing Cell”) that you want to manipulate to achieve the desired result.

Excel then iteratively adjusts the value in the Adjustable Cell until the Set Cell reaches the Target Value.

To use Goal Seek in Excel:

  1. Go to the “Data” tab.
  2. In the “Data Tools” group, click on “What-If Analysis.”
  3. Choose “Goal Seek” from the dropdown menu.
  4. In the Goal Seek dialog box, specify the Set Cell, Target Value, and Adjustable Cell.
  5. Click “OK,” and Excel will perform the calculation to find the input value needed to meet the specified goal.

What if it worked like an Excel Formula?

The VBA code for the Goal Seek formula is not that complicated. 
destCell.GoalSeek Goal:=endValue, ChangingCell:=sourceCell
Where (i) destCell, sourceCell are Range and (ii) and endValue is a Double

This will change to structure: 

It involves a UDF, Sub Procedure and Custom Ribbon

In short, the GoalSeek function is a custom worksheet function that generates a human-readable description of a Goal Seek operation. The RunGoalSeekMacro subroutine iterates through open workbooks, identifies Goal Seek messages in cells, and executes the Goal Seek operation based on the information extracted from these messages.

With GoalSeek Function: It’s like leaving a note for Excel, saying “I want this cell to be a specific number, and I’ll change another cell to make it happen.” With RunGoalSeekMacro Subroutine: It’s your assistant going through your Excel sheets, finding those notes, and making the changes you asked for automatically.

VBA Codes

Save these two codes in new module in your custom addin file. For easy access to launch the RunGoalSeekMacro() you can create custom tab group in the Excel Ribbon like I used in the image above. 

VBA Code for Custom GoalSeek Function: 

            Function GoalSeek(destinationCell As Range, endValue As Double, sourceCell As Range) As String
GoalSeek = "Set Cell " & destinationCell.Address(0, 0) & " to Value " & endValue & " By Changing Cell " & sourceCell.Address(0, 0)
End Function

VBA Code for Custom RunGoalSeekMaro Sub Procedure: 

            Sub RunGoalSeekMaro()
On Error Resume Next

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cell As Range
    Dim formulaText As String
    Dim destCell As Range
    Dim endValue As Double
    Dim sourceCell As Range
    
    ' Iterate through all open workbooks
    For Each wb In Application.Workbooks
        ' Skip add-in workbooks or hidden workbooks
        If Not wb.IsAddin And wb.Windows(1).Visible Then
            ' Iterate through all worksheets in the current workbook
            For Each ws In wb.Worksheets
                ' Iterate through all cells in the current worksheet
                For Each cell In ws.UsedRange
                    ' Check if the cell contains the GoalSeek custom formula result
                    If InStr(1, cell.Value, "Set Cell ") > 0 And InStr(1, cell.Value, " to Value ") > 0 And InStr(1, cell.Value, " By Changing Cell ") > 0 Then
                        ' Get the parameters from the formula result
                        formulaText = cell.Value
                        
                        ' Extract destination cell, end value, and source cell
                        Set destCell = Range(split(split(formulaText, "Set Cell ")(1), " to")(0))
                        endValue = CDbl(split(split(formulaText, "to Value ")(1), " By Changing Cell")(0))
                        Set sourceCell = Range(split(split(formulaText, "By Changing Cell ")(1), ")")(0))
                        
                        ' Perform Goal Seek
                        With destCell
                            .GoalSeek Goal:=endValue, ChangingCell:=sourceCell
                        End With
                    End If
                Next cell
            Next ws
        End If
    Next wb
End Sub