If you’ve ever set up a user-defined function (UDF) or created shapes that trigger macros, you might have come across suggestions for Application.Caller. This application-level property has one purpose: It tells you where your VBA macro was called (or “triggered”) from. That information is valuable if you have slightly different procedures for the same macro depending on the context. We’ll demonstrate a couple ways to use Application.Caller to make your macros better.

The 3 Variable Types Returned

The Application.Caller property can return three possible variable types, so be careful using it when you’re explicitly dimensioning your variable types or using static variable types.

The three types are

  • String
  • Range
  • Error

Strings occur when the caller of the VBA module has a name, usually a shape or a form control object. The Application.Caller property itself will be a string and will return the name of the object, so there is no need to access any .Name property of .Caller subproperty. If you have a named Shape that calls a macro, you can set a variable callingShape like this to return the name of the shape that called the macro:

callingShape = Application.Caller

The property can take on a second variable type for ranges. Here, it becomes a full range object, including the .Row, .Column, and, importantly, .Worksheet properties. The range can be either a single cell or an array depending on whether the UDF is placed in a single cell or is applied to the sheet as an array function.

You can set the callingCellRow, callingCellColumn, and callingCellSheetName variables like this:

callingCellRow = Application.Caller.Row

callingCellColumn = Application.Caller.Column

callingCellSheetName = Application.Caller.Worksheet.Name

The Row and Column variables will be of type Long (a numeric) and the worksheet’s name is a String. You can return the Address of the cell that called your UDF by using Application.Caller.Address, which will give you both the Row(s) and Column(s) involved as a String (for example, $G$2).

The third variable type that Application.Caller can take on is an Error, which generally occurs if the command returns neither a string nor a range object. The error will be Error 2023. If you test your code and get 2023 errors, they’re likely coming from a bad Application.Caller property somewhere in your code.

Common Use: Generalizing Interactive UDFs

Excel and VBA are powerful and extensive, but we don’t always find that perfect function for our needs. In those cases, we usually end up writing our own functions to handle whatever custom application we’re building.

Furthermore, I sometimes find it easier to script a few lines of VBA than try to figure out how to nest built-in formulas to achieve the result I want, especially when I need to start working with nested ANDs and IF functions.

Sometimes a very small tweak, based on which cell has called the macro, can help us generalize the UDF to more cases without duplicating code and writing separate functions. An example from busi\ness could be a function that is additive above the Gross Income line on an Income Statement, since each line item in that section of the statement adds revenue, while the same function would be subtractive below the Gross Income line, since each line item in that section is an expense.

UDFs can also be more interactive with the worksheet if we use Application.Caller by leveraging the resultant Range object’s .Row and .Column properties. One example might be different insurance costs for different employees depending on the number of hours worked that week.

Table with different rates for different employees

Suppose the Rate < 50h and Rate > 50h are confidential, but employees should be able to edit the Special Hours Worked Function. To protect privacy, the employer hides the two Rate columns and then allows editing only of specific ranges on the sheet. Using the two hidden columns, protected ranges in the UDF could confuse users. To avoid confusion, you could import the rates using Application.Caller:

If hoursWorked > 50 Then
    insuranceRate = Cells(Application.Caller.Row, Application.Caller.Column - 1)
Else
    insuranceRate = Cells(Application.Caller.Row, Application.Caller.Column - 2)
End If

Now the rates can remain confidential and the inputs never appear in the UDF for the user. The function can be edited without confusion. This is a great, and pretty common, use case for the Application.Caller function in an Excel workbook.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Common Use: Shapes

Another quite common use case is to determine which Shape or Form Control Object called a macro. Building a visually-appealing dashboard in Excel often ends up with Shapes used as buttons, because Shapes are easy to customize with the Excel GUI - and userforms aren’t.

If you have 3 buttons on a sheet, you can easily assign them to 3 separate macros, but this would be inefficient if most of the code is the same. There’s a principle in programming called DRY, which stands for Don’t Repeat Yourself. Having 3 buttons point to 3 macros that are almost identical, with only minor calculation differences, violates this principle.

Instead, you could assign them all to the same macro and use the name of the calling Shape to determine which part of the macro to use.

Three colored buttons for different calculations
Three colored buttons for different calculations

Let’s say your buttons are named rateButton, taxButton, and payButton and they all call the same macro. Now you can use a single macro rather than 3 separate ones with lots of repetition.

The Select Case block might look like this:

Sub ProcessButtons()
callingButton = Application.Caller

Select Case callingButton
    
    Case "rateButton"
        'calculate rate
        
    Case "taxButton"
        'calculate tax
        
    Case "payButton"
        'calculate pay
        
End Select

End Sub

Bonus Use Case: Debugging

If you are given a very complex spreadsheet to debug, you may struggle to find the origin of a cascading #VALUE! error. If you check the VBA code and find a UDF, you could try using the Application.Caller.Worksheet.Name and Application.Caller.Address properties to locate the offending cell. From there, you may be able to figure out which inputs to the function are wrong based on their cell references.

This method is particularly useful if you are given a spreadsheet with cryptic input names and zero context on the UDF inputs. In this case you may have to rebuild the original developer’s thought process, and without knowing where the original UDFs are entered into the sheets, it could feel impossible to figure out the various interactions and calculations taking place.

To wrap things up, there are three variable types returned by Application.Caller: Strings, Ranges, and Errors.

The Ranges are full-bodied objects and contain all the properties of a Range, including Row, Column, Address, and Worksheet.Name properties. You can use these to change the behavior of a user-defined function depending on which cell called the function or even which sheet the cell that called the UDF was on.

Shape names are useful for determining which buttons on a visual dashboard have called the function. It helps you avoid one-button-one-macro code bloat. It can also be used to change your macro logic based on which form control radio button or checkboxes are checked.

Application.Caller can also be useful for debugging complex spreadsheets.

Play around with it until you get comfortable using it. Application.Caller isn’t used too often but it ought to be. It can shorten the amount of lines of code you write and it gives your VBA macros a polished look. If you found this helpful, I hope you’ll subscribe using the form below.