Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel cell from which a Function is called [duplicate]

Tags:

excel

vba

cell

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.

like image 988
Dan Dascalescu Avatar asked Apr 05 '11 22:04

Dan Dascalescu


People also ask

How do you check if a name appears twice in Excel?

Select the data you want to check for duplicates. This can be a column, a row or a range of cells. On the Home tab, in the Styles group, click Conditional Formatting > Highlight Cells Rules > Duplicate Values…

Which term is used when a formula is copied from one cell to another?

Relative references change when a formula is copied to another cell.


1 Answers

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

like image 151
Charles Williams Avatar answered Nov 16 '22 01:11

Charles Williams