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, Feb 13 '10 at 22:45
`ThisCell` https://msdn.microsoft.com/en-us/library/office/ff834969.aspx — Slai, Nov 10 '16 at 20:44

score 28 · Answer 1 · edited May 08 '18 at 21:48

28

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)

edited May 08 '18 at 21:48

Community

answered Feb 13 '10 at 23:37

Fionnuala

Just found out that I asked the same question: [How do I find out which cell a VBA Function was called from](http://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called) – Dan Dascalescu Apr 05 '11 at 22:34

1 Answers1