Determining the immediate caller of a function
 

I posted this yesterday and got several responses (thanks
Alan and Dave), but not quite what I was looking for,
probably due to my poor explanation. I'll try and explain
better.

I'm trying to determine within a function whether it has
been called directly from Excel or from another VBA
sub/function. I'm looking for the "immediate" caller, not
the "ultimate" caller. For example:

Say my function is defined as Func1(x)

Case 1
A cell in a worksheet has the formula "=Func1(x)". I want
to be able to tell within the function that it has been
called directly from Excel.

Case 2
I have another function called Func2(y) and, within this
function I have a call to Func1(x). I then have a cell in
a worksheet has the formula "=Func2(y)". I want to be able
to tell within the Func1(x) function that it has NOT been
called directly from Excel, but from another function.

I've tried looking for ways to access the
call stack, but have had no luck. I don't think I can get
the info I want from the Application.Caller property
either.

Does anybody have any clues on this one?

Thanks in advance,
Ryan

Determining the immediate caller of a function
 

Well, I haven't thoroughly explored the kinks, but I do use something
like the following:

Function Func1()
Dim CalledDirectlyFromWorksheet As Boolean, p As Object
CalledDirectlyFromWorksheet = False
If TypeName(Application.Caller) = "Range" Then
Set p = Application.Caller
If InStr(1, p.Formula, "Func1") = 2 Then
CalledDirectlyFromWorksheet = True
End If
End If
Debug.Print CalledDirectlyFromWorksheet
Func1 = Application.Sum(1, 2, 3)
End Function

Function Func2(a)
Func2 = 2 * a
End Function

Compare =Func1() with =Func2(Func1())

Hope it helps.

Alan Beban

Ryan Poth wrote:
I posted this yesterday and got several responses (thanks
Alan and Dave), but not quite what I was looking for,
probably due to my poor explanation. I'll try and explain
better.

I'm trying to determine within a function whether it has
been called directly from Excel or from another VBA
sub/function. I'm looking for the "immediate" caller, not
the "ultimate" caller. For example:

Say my function is defined as Func1(x)

Case 1
A cell in a worksheet has the formula "=Func1(x)". I want
to be able to tell within the function that it has been
called directly from Excel.

Case 2
I have another function called Func2(y) and, within this
function I have a call to Func1(x). I then have a cell in
a worksheet has the formula "=Func2(y)". I want to be able
to tell within the Func1(x) function that it has NOT been
called directly from Excel, but from another function.

I've tried looking for ways to access the
call stack, but have had no luck. I don't think I can get
the info I want from the Application.Caller property
either.

Does anybody have any clues on this one?

Thanks in advance,
Ryan

Determining the immediate caller of a function
 

Thanks Alan, looks like that (or something similar) will
work for me. I appreciate the help.
Ryan

-----Original Message-----
Well, I haven't thoroughly explored the kinks, but I do
use something
like the following:

Function Func1()
Dim CalledDirectlyFromWorksheet As Boolean, p As
Object
CalledDirectlyFromWorksheet = False
If TypeName(Application.Caller) = "Range" Then
Set p = Application.Caller
If InStr(1, p.Formula, "Func1") = 2 Then
CalledDirectlyFromWorksheet = True
End If
End If
Debug.Print CalledDirectlyFromWorksheet
Func1 = Application.Sum(1, 2, 3)
End Function

Function Func2(a)
Func2 = 2 * a
End Function

Compare =Func1() with =Func2(Func1())

Hope it helps.

Alan Beban

Ryan Poth wrote:
I posted this yesterday and got several responses
(thanks
Alan and Dave), but not quite what I was looking for,
probably due to my poor explanation. I'll try and
explain
better.

I'm trying to determine within a function whether it
has
been called directly from Excel or from another VBA
sub/function. I'm looking for the "immediate" caller,
not
the "ultimate" caller. For example:

Say my function is defined as Func1(x)

Case 1
A cell in a worksheet has the formula "=Func1(x)". I
want
to be able to tell within the function that it has been
called directly from Excel.

Case 2
I have another function called Func2(y) and, within
this
function I have a call to Func1(x). I then have a cell
in
a worksheet has the formula "=Func2(y)". I want to be
able
to tell within the Func1(x) function that it has NOT
been
called directly from Excel, but from another function.

I've tried looking for ways to access the
call stack, but have had no luck. I don't think I can
get
the info I want from the Application.Caller property
either.

Does anybody have any clues on this one?

Thanks in advance,
Ryan


.