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
- Common Use: Generalizing Interactive UDFs
- Common Use: Shapes
- Bonus Use Case: Debugging
The 3 Variable Types Returned
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
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 = Application.Caller
The property can take on a second variable type for ranges. Here, it becomes a full range object, including the
.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 = 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,
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
.Column properties. One example might be different insurance costs for different employees depending on the number of hours worked that week.
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 full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.
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 full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.
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
Let’s say your buttons are named
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.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.