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.