VBA & Macro Problems

Find the Calling Cell Address in an Excel VBA Function

Emma BrownEmma Brown Nov 14, 2026 869 views

Question details

Identify the row and column of the worksheet cell that calls a VBA function.

Find the Calling Cell Address in an Excel 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.
Before you start

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.

Solution 1Recommended

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.

1
Open a VBA module

In Excel, open Developer > Visual Basic, then choose Insert > Module.

2
Add the function

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

3
Enter the formula

In the worksheet cell above the values to total, enter =SumBelowSameColor(). The function starts on the next row in the same column.

4
Inspect the caller position

In the VBA module, use caller.Row and caller.Column for the row and column numbers, or caller.Address for the cell address.

Use Application.Caller to locate the formula cell
Recalculation and fill colors: Changing a fill color alone may not trigger recalculation. Press F9 to recalculate the volatile function. Interior.Color reads the cell's fill property, not necessarily the color displayed by conditional formatting.
Free Microsoft Office alternative

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.

Open and edit Excel XLSX spreadsheetsUse familiar spreadsheet tools for formulas and cell formattingMove everyday documents between WPS Office and Microsoft OfficeUse a free, lightweight office suite
microsoft office alternative - wps office

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.