MS-Excel: Retrieve Row Number where VBA Function is located

user_14671209 0
Viewed 198

I hope I'm asking this in the correct forum:

I'm writing a UDF in VBA for MS-Excel; it basically builds a status message for the transaction on that row. It steps through a series of IF statements, evaluating cell values in different columns FOR THAT ROW.

However, this UDF will reside in multiple rows. So it might be in C12, C13, C14, etc. How would the UDF know which row to use? I'm trying something like this, to no effect

Tmp_Row = Application.Evaluate("Row()")

which appears to return a null

What am I missing here ?

Thanking everyone in advance

user-defined-functions row-number vba excel
2 Answers

Application.Caller is seldom used, but when a UDF needs to know who called it, it needs to know about Application.Caller.

Except, you cannot just assume that a function was invoked from a Range. So you should validate its type using the TypeOf...Is operator:

Dim CallingCell As Excel.Range
If TypeOf Application.Caller Is Excel.Range Then
    'Caller is a range, so this assignment is safe:
    Set CallingCell = Application.Caller
End If

If CallingCell Is Nothing Then
    'function wasn't called from a cell, now what?
Else
    'working row is CallingCell.Row
End If

Suggestion: make the function take its dependent cells as Range parameters (if you need the Range metadata; if you only need the values then take in Double, Date, String parameters instead) instead of making it fetch values from the sheet. This decouples the worksheet layout from the function's logic, which in turn makes it much more flexible and easier to work with - and won't need any tweaks if/when the worksheet layout changes.

user_11885130

Application.ThisCell

MS Docs: Returns the cell in which the user-defined function is being called from as a Range object.

You can put it to the test using the following code:

Function testTC()
    testTC = Application.ThisCell.Row
End Function

In Excel use the formula

   =testTC()

and (Cut)Copy/Paste to various cells.

user_98140690
Related