62

How can I get the cell where my VBA Function is called from?

In other words, what is the VBA equivalent for INDIRECT(ROW(), COLUMN()) ?

I'm not looking for ActiveCell.

What I want to do is have a simple function ThisRow_Col(rColumn As String) return the column X of the row it's called from. Say in B2 I call =ThisRow_Col("A"), it should return the value of A2. This should work regardless of which cell active.

EDIT: Thanks Charles for the answer: Application.Caller. The following code gets the column X of the current row, independent of where the selection is:

Function ThisRow_Col(rColumn As Range)
    ' Return INDIRECT(rcolumn & ROW())

    ThisRow_Col = Application.Caller.Worksheet.Cells(Application.Caller.Row, rColumn.Column).Value

End Function

Note that passing the column as a Range (ThisRow_Col(A1)) is better than passing it as a string, because Excel can automatically update the formulas if you move or insert columns. Of course the 1 row of A1 is just a convention.

casperOne
  • 73,706
  • 19
  • 184
  • 253
Dan Dascalescu
  • 143,271
  • 52
  • 317
  • 404

1 Answers1

85

Application.Caller returns the range object that the UDF is called from.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • 5
    @dandv, @phoog, Here is some more detail about Application.Caller: http://stackoverflow.com/questions/3861431/vba-how-to-get-the-last-used-cell-by-vba-code-when-the-last-error-occured-in-a-w/3874340#3874340 – jtolle Jun 02 '11 at 14:22