How do you get calling cell for custom function

[Gregory Cmar]

I have written a custom function that returns database data to my
spreadsheet. The follwoing code snippet describesthe process. I test the
variable being passed in. If it is an object (cell range like A1:A3), I get
that address and process results based upon the values in those cells.

My question is, how do I get the address of the calling cell (the cell where
I entered the formula)? I want to test whether it is an array formula.

Public Function GetSqlData(ByVal ProcName As Variant) As Variant
Dim sProcName As Variant
If IsObject(ProcName) = True Then
Set xRange = ProcName
sProcName = GetValues(xRange)
Else
sProcName = ProcName
End If
GetSqlData = sProcName
End Function

Thanks,

Greg

 

[Bob Phillips]

As an example

MsgBox Application.Caller.Address

 

[AA2e72E]

Look up Application.Caller in the help files;there is an example.

 

[Dave Peterson]

Application.caller

will return the address of the cell with the formula.

 

[Tom Ogilvy]

Typo alert

Application.caller

returns a reference to the cell containing the formula.

Application.Caller.Address

would return the address.