Find the Calling Cell Address in an Excel VBA Function
Question details
Identify the row and column of the worksheet cell that calls a VBA function.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet function totals cells below its formula cell until the background color changes.
- Observed behavior
- The function needs the formula cell's position to locate and inspect the cells below it.
Make sure the workbook permits macros and is saved as an Excel Macro-Enabled Workbook (.xlsm). Decide whether the stopping color should match the formula cell or the first cell below it.
Use Application.Caller to locate the formula cell
Use this approach when a VBA worksheet function needs to find its own cell and inspect the cells below it.
Application.Caller returns the calling worksheet cell when the function is used in a cell formula. Its Row, Column, and Address properties identify that cell.
The example compares each cell's fill with the formula cell's fill and totals numeric values until the fill changes. If the first cell below should define the starting color instead, change the reference color accordingly.
In Excel, open Developer > Visual Basic, then choose Insert > Module.
Paste this code into the module: Function SumBelowSameColor() As Variant Dim caller As Range Dim current As Range Dim r As Long Dim total As Double Dim startColor As Long On Error GoTo InvalidCaller Application.Volatile Set caller = Application.Caller If caller.CountLarge <> 1 Then GoTo InvalidCaller startColor = caller.Interior.Color For r = caller.Row + 1 To caller.Worksheet.Rows.Count Set current = caller.Worksheet.Cells(r, caller.Column) If current.Interior.Color <> startColor Then Exit For If Not IsError(current.Value) Then If IsNumeric(current.Value) Then total = total + current.Value End If Next r SumBelowSameColor = total Exit Function InvalidCaller: SumBelowSameColor = CVErr(xlErrValue) End Function
In the worksheet cell above the values to total, enter =SumBelowSameColor(). The function starts on the next row in the same column.
In the VBA module, use caller.Row and caller.Column for the row and column numbers, or caller.Address for the cell address.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Excel-format support makes it easy to move everyday workbook work between Office suites; it does not fix VBA behavior in Microsoft Excel.

Frequently Asked Questions
How do I get the address of the cell that called a VBA function?
In a worksheet VBA function, assign Application.Caller to a Range variable, then use the variable's Address property. Use Row and Column for its numeric position.
Why does the total not update after I change a cell's fill color?
A fill change alone may not trigger Excel recalculation. Add Application.Volatile to the function and press F9 to recalculate the worksheet.
Can the function detect colors applied by conditional formatting?
The example uses Interior.Color, which reads the cell's fill property and may differ from the color shown by conditional formatting. Do not assume it reflects the displayed conditional-format color.
Can a worksheet VBA function add or remove borders?
A function called from a worksheet formula should return a value rather than change worksheet formatting. Use a separate VBA Sub run from Developer > Macros to apply or remove borders.
How do I keep the VBA function when I save the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). When reopening the file, enable macros only if you trust the workbook.




