How to identify the row from which a UDF is being executed?

[TCH]

Hello,

I need to identify the row number from which my UDF is being executed .

I want reference cells("the row" , 1), but don't know how to reference the row number.

Obviously, ActiveCell.Row is not appropriate but was used as an example.

Thanks for your help.

Ted H.


Function OnTime_30_OnTime()

Application.Volatile True

Analyst = Cells(ActiveCell.Row, 1)

OnTime_30_OnTime = Worksheets(Analyst).Range("AS1")

End Function

 

[RoryA]

It would be Application.Caller.Row or Application.ThisCell.Row but neither is very good design - you'll probably have to make your function volatile if you adopt this form of indirect referencing.

 

[Domenic]

Have you considered using the native and volatile function INDIRECT? So, for example...

=INDIRECT("'"&A2&"'!AS1")

 

[TCH]

Rory,

Thanks for your reply.

My code now reads: Analyst = Cells(Application.Caller.Row, 1) and works perfectly.

Thanks again.

Ted H

 

[TCH]

Domenic,

Thanks also for your reply.

I will your method as it is more straight-forward and won't involve using VBA code as I had originally envisioned.

Thank you.

Ted H