I'm looking for a way to reliably detect whether a UDF is called from a
worksheet as opposed to a call from VBA - without explicitly declaring a
parameter in the calling argument for that purpose (ie. an Optional Boolean
variable for instance).

I am currently using a unique combination of existing parameters along with
"ActiveCell" to perform the discrimination, but I'm concerned that under some
improbable, yet possible conditions a false positive could occur. I was
hoping that I could use "Application.ThisCell.Address", where I could test
for "Nothing" if it was called by VBA. Unfortunately, a runtime error occurs
at the test when it's called by VBA, citing an error in the "Method" ThisCell
(thought it was a property?). Apparently, it's not just set to "Nothing" if
called by VBA, it must not exist.

Any suggestions? Thanks.

Function Mush() As String
MsgBox TypeName(Application.Caller)
Mush = "Made from oatmeal"
End Function

Sub MakeBreakfast()
MsgBox Mush
End Sub

'Also call it from the worksheet and see what is displayed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"mickey"
wrote in message
I'm looking for a way to reliably detect whether a UDF is called from a
worksheet as opposed to a call from VBA - without explicitly declaring a
parameter in the calling argument for that purpose (ie. an Optional Boolean
variable for instance).

I am currently using a unique combination of existing parameters along with
"ActiveCell" to perform the discrimination, but I'm concerned that under some
improbable, yet possible conditions a false positive could occur. I was
hoping that I could use "Application.ThisCell.Address", where I could test
for "Nothing" if it was called by VBA. Unfortunately, a runtime error occurs
at the test when it's called by VBA, citing an error in the "Method" ThisCell
(thought it was a property?). Apparently, it's not just set to "Nothing" if
called by VBA, it must not exist.

Any suggestions? Thanks.

i'LL CHECK IT OUT - THANKS.

"Jim Cone" wrote:

Function Mush() As String
MsgBox TypeName(Application.Caller)
Mush = "Made from oatmeal"
End Function

Sub MakeBreakfast()
MsgBox Mush
End Sub

'Also call it from the worksheet and see what is displayed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"mickey"
wrote in message
I'm looking for a way to reliably detect whether a UDF is called from a
worksheet as opposed to a call from VBA - without explicitly declaring a
parameter in the calling argument for that purpose (ie. an Optional Boolean
variable for instance).

I am currently using a unique combination of existing parameters along with
"ActiveCell" to perform the discrimination, but I'm concerned that under some
improbable, yet possible conditions a false positive could occur. I was
hoping that I could use "Application.ThisCell.Address", where I could test
for "Nothing" if it was called by VBA. Unfortunately, a runtime error occurs
at the test when it's called by VBA, citing an error in the "Method" ThisCell
(thought it was a property?). Apparently, it's not just set to "Nothing" if
called by VBA, it must not exist.

Any suggestions? Thanks.

It looks like it will work, it returns a "Range" when called by the
worksheet. Not sure what it returns if called by VBA: it's not "String".
But, all I need do is trap on "Range" anything else can be assumed to be a
VBA call.

Thanks Jim



"Jim Cone" wrote:

Function Mush() As String
MsgBox TypeName(Application.Caller)
Mush = "Made from oatmeal"
End Function

Sub MakeBreakfast()
MsgBox Mush
End Sub

'Also call it from the worksheet and see what is displayed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"mickey"
wrote in message
I'm looking for a way to reliably detect whether a UDF is called from a
worksheet as opposed to a call from VBA - without explicitly declaring a
parameter in the calling argument for that purpose (ie. an Optional Boolean
variable for instance).

I am currently using a unique combination of existing parameters along with
"ActiveCell" to perform the discrimination, but I'm concerned that under some
improbable, yet possible conditions a false positive could occur. I was
hoping that I could use "Application.ThisCell.Address", where I could test
for "Nothing" if it was called by VBA. Unfortunately, a runtime error occurs
at the test when it's called by VBA, citing an error in the "Method" ThisCell
(thought it was a property?). Apparently, it's not just set to "Nothing" if
called by VBA, it must not exist.

Any suggestions? Thanks.

FYI when called by VBA TypeName(Application.Caller) returns "Error".

"Jim Cone" wrote:

Function Mush() As String
MsgBox TypeName(Application.Caller)
Mush = "Made from oatmeal"
End Function

Sub MakeBreakfast()
MsgBox Mush
End Sub

'Also call it from the worksheet and see what is displayed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"mickey"
wrote in message
I'm looking for a way to reliably detect whether a UDF is called from a
worksheet as opposed to a call from VBA - without explicitly declaring a
parameter in the calling argument for that purpose (ie. an Optional Boolean
variable for instance).

I am currently using a unique combination of existing parameters along with
"ActiveCell" to perform the discrimination, but I'm concerned that under some
improbable, yet possible conditions a false positive could occur. I was
hoping that I could use "Application.ThisCell.Address", where I could test
for "Nothing" if it was called by VBA. Unfortunately, a runtime error occurs
at the test when it's called by VBA, citing an error in the "Method" ThisCell
(thought it was a property?). Apparently, it's not just set to "Nothing" if
called by VBA, it must not exist.

Any suggestions? Thanks.

And when called from a button from the Forms toolbar it returns "String".
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"mickey"
wrote in message
FYI when called by VBA TypeName(Application.Caller) returns "Error".