My_Head_Hurts


I have a need to check other values in the same row, and/or same column as the cell being processed. My User Defined Function therefore needs to know what cell is invoking it. It is certainly not ActiveCell.

My function works if I pass it a string (e.g. "H12") to tell the UDF what cell it is, but that's too messy to populate through hundreds of cells.

Does anyone know how this can be done

Thanks in advance... Jeff.




Re: Excel: Can a UDF tell what cell it was invoked from?

Andy Pope


Hi,

This will give you information about the cell.

Public Function udfMyFunc()

Debug.Print "Cell address", Application.Caller.Address
Debug.Print "Worksheet", Application.Caller.Parent.Name
Debug.Print "Workbook", Application.Caller.Parent.Parent.Name

End Function







Re: Excel: Can a UDF tell what cell it was invoked from?

My_Head_Hurts

Brilliant. There had to be a way - just couldn't find it.

Many thanks Andy.






Re: Excel: Can a UDF tell what cell it was invoked from?

My_Head_Hurts

Just to make the information in this thread a little more useful, here's the code I finished up with...

Code Snippet

Dim liRow As Integer
Dim liColumn As Integer

Dim lsAddress as String


' Work out the row and column of the calling cell.

' The caller address field is expected to contain a string like "$H$12",
' but we need the integer equivalent values.

lsAddress = Application.Caller.Address
liColumn = Range(lsAddress).Column ' get the integer equivalent of $H or $AD...
liRow = Range(lsAddress).Row