Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use index and match function for determining the value? | Excel Discussion (Misc queries) | |||
User_Form's Caller | Excel Discussion (Misc queries) | |||
Application.Caller | Excel Discussion (Misc queries) | |||
DDE and application.caller help | Excel Programming | |||
Determining whether function called by Excel or VBA | Excel Programming |