Get cell reference of UDF Caller

[whitelined]

Hi,

I have a UDF function to do some calculation. Based on conditions of that calculation I want to change the colour of the cell it was called on - can't do it with conditional formatting. Is it possible to get the calling cell range object?

Cheers

Aaron

 

[Weaver]

Could you not pass it as an argument?

Having said that, I'd be interested to see if there's another way to pick up this detail.

 

[RoryA]

It's Application.Caller but you can't do that from a UDF called from a cell.

 

[Colin Legg]

You can pick up the caller cell, eg:

Public Function MyTest() As String
    MyTest = "hello"
 
    If TypeOf Application.Caller Is Excel.Range Then MsgBox Application.Caller.Address
 
End Function

But when a UDF is called from a cell, it cannot be used to change the formatting of the cell.

For further information see here:
http://www.cpearson.com/excel/writingfunctionsinvba.aspx