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.
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…
Relative references change when a formula is copied to another cell.
Application.Caller
returns the range object that the UDF is called from.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With