Determining the immediate caller of a function

  • Thread starter Thread starter Ryan Poth
  • Start date Start date
R

Ryan Poth

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
 
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
 
Thanks Alan, looks like that (or something similar) will
work for me. I appreciate the help.
Ryan
 
Back
Top