Excel cell from which a Function is called

excel vba cell
Dan Dascalescu picture Dan Dascalescu · Apr 6, 2011 · Viewed 72.6k times · Source

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.

Answer

Charles Williams picture Charles Williams · Apr 6, 2011

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

Related questions

Convert cells(1,1) into "A1" and vice versa

I am working on an worksheet generator in Excel 2007. I have a certain layout I have to follow and I often have to format cells based on input. Since the generator is dynamic I have to calculate all kinds of …

Read More
VBA setting the formula for a cell

I'm trying to set the formula for a cell using a (dynamically created) sheet name and a fixed cell address. I'm using the following line but can't seem to get it working: "=" & strProjectName & "!" & Cells(2, 7).Address Any advice …

Read More
Get value of a merged cell of an excel from its cell address in vba

How to get the value of a merged cell of an excel having range address like "$B$4:$B$11" in vba

Read More