UDF called by another UDF sees Caller as an Object

[avr5iron]

In the attached file TestFunc1 is a UDF called from the workbook. TestFunc1 calls UDF TestFunc2 and returns its result. TestFunc2 returns True of it is called by an Object, or False if not.
Since TestFunc2 is called by TestFunc1, why does TestFunc2 see its caller as an Object?

[p45cal]

I'm guessing here, but Caller seems to be an Application level property, which, according to help "Returns information about how Visual Basic was called" which in this instance is A custom function entered in a single cell, and so the Caller is A Range object specifying that cell, which in this case is cell C3 on that sheet (an object). I reckon, that no matter where you use application.caller, (in a sub or function) it'll return what originally started vba execution, until code execution is finished. In this case, it's cell C3,an object, so your function returns True.

I think the important words here are how Visual Basic was called, note hat it's not how a macro (or function) was called

[avr5iron]

Thank you for the info/insight. I have several procedures written (incorrectly, as I now know) to interpret whether they've been called from a range or another procedure. That info allows for case-specific efficiencies in passing and returning arrays. Do you know of any other way of flagging that info?

[avr5iron]

Thank you for the info/insight. I have several procedures written (incorrectly, as I now know) to interpret whether they've been called from a range or another procedure. That info allows for case-specific efficiencies in passing and returning arrays and array-like (i.e., range) data. Do you know of any other way of flagging that info?

[SamT]

Public Function TestFunc1() 'Called by range object
TEstFunc2 "TestFunc1"
End Function


Function TestFunc2(Optional CallerName as String)

If not CallerName = "" THen
MsgBox "This Function was called by " & CallerName
End Function

[p45cal]

Yes, of course, you can pass any number of arguments to another sub or function. Not tested, but I think you can use optional arguments (the last ones) in udfs (even if they are used both from worksheet formulae and from other subs/functions).
So, to interpret whether they've been called from a range or another procedure, inside the procedure call the function/sub with an argument indicating that that is the case. The only limit is your imagination; it could be a string even giving the name of the sub it was called from.

edit post posting: SamT beat me to it.

[Paul_Hossler]

You can't access the call stack in VBA to see what the internals look like

As others have said, it depends on how complicated you want to make it

This just used a passed Optional flag = True if calling from a macro, or not set if used as a WS function



Option Explicit
Option Base 1

Function TestFunc1(vArr As Range) As Variant
Dim r As Long, c As Long

r = vArr.Rows.Count
c = vArr.Columns.Count

TestFunc1 = testFunc2(r * c, True)
End Function

Function testFunc2(vX As Variant, Optional CalledByVBA As Boolean = False) As Variant
If CalledByVBA Then
testFunc2 = vX
Else
testFunc2 = "In a Worksheet"
End If
End Function




If you're calling it from a sub .Caller returns an error you can trap. I used TypeName, but there's amany ways to do it

testFunc3 is on the worksheet also



Sub TestSub()

MsgBox testFunc3(1234)
End Sub
Function testFunc3(x As Long) As Variant
Dim s As String

s = TypeName(Application.Caller)
If s = "Error" Then
testFunc3 = "Called by VBA"

Else
testFunc3 = 2 * x
End If
End Function

[avr5iron]

Thank you all. I like the optional argument solution.