How to get address of cell which calls a VBA Functions in a Excel sheet

Question

Is there a function to get the address of a cell which call a VBA function ?

I used "Application.ActiveCell.Address" , but found that this method is not a correct way to get the address of calling cell.

e.g: If we have excel sheet with saved VBA functions, Once you reopen the sheet, the above method will not work.

please help

nimo

@nimo, is the function a UDF? Can you maybe give some code as an example of what you are trying to do? — Mark Nold
ThisCell msdn.microsoft.com/en-us/library/office/ff834969.aspx — Slai

Fionnuala Fionnuala · Accepted Answer · 2010-02-13T23:37:35

You can use Application.Caller, but you would probably be best to pass the cell to the function.

   With Application.Caller
        CallerRows = .Rows.Count
        CallerCols = .Columns.Count
        CallerAddr = .Address
   End With

To pass the cell:

 =SomeFunction(A1)

Where the code has:

SomeFunction(r)