How do you get calling cell for custom function
 

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

How do you get calling cell for custom function
 

As an example

MsgBox Application.Caller.Address


Gregory Cmar wrote:
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

How do you get calling cell for custom function
 

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

"Gregory Cmar" wrote:

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

How do you get calling cell for custom function
 

Application.caller

will return the address of the cell with the formula.

Gregory Cmar wrote:

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

--

Dave Peterson

How do you get calling cell for custom function
 

Typo alert

Application.caller

returns a reference to the cell containing the formula.

Application.Caller.Address

would return the address.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
Application.caller

will return the address of the cell with the formula.

Gregory Cmar wrote:

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

--

Dave Peterson