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

Question

Hi,

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

Answer 1

How about searching the sheet for the function?

Answer 2

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

See: http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

To pass the cell:

 =SomeFunction(A1)

Where the code has:

SomeFunction(r)