Few UDF (VBA) questions - are there tricks for this?

[TomCon]

Or, at least i'd think of them as tricks, since it is not obvious to me!

o Is there any way to find the address of the cell that is currently calling the UDF? Say, you set a breakpoint, and want to know which cell it is now calculating when it hits the breakpoint.
o Is there a way to set a conditional breakpoint on a particular cell as calling the UDF? For instance, only break when this function is called from cell A5, etc? (In the VBA editor).
o Is there a way to get the text of the argument that is in the UDF call from the sheet? For instance, if my UDF is declared as Function MyFun(arg1). Say, on the sheet it is called with =MyFun(Max(A5:A10)). In the function execution code, i will get arg1 as the value of Max(A5:A10). Within UDF(VBA) code, can i get at the text for the argument, which in this example is "Max(a5:a10)"?

Thanks,
tom

 

[Scott Huish]

It's hard to tell from that description what you are trying to accomplish exactly. But I think this answers your questions:

Function MyFunction(f As String) As String
Dim a As Range
Set a = Parent.Caller
If a.Address = "$A$5" Then Stop
MyFunction = f
End Function

 

[TomCon]

Thanks much! That answers the first two bullets. Great, glad to see it exists; i feared it did not. These are fairly general questions, but i have run into this need several times, so just thought i'd ask.

On the third bullet, does anybody know if there is a way to do that?

Thanks!
Tom

 

[Scott Huish]

I gave you that too, just pass the inside as a string.


=myfunction("MAX(A1:A5)")

 

[shg]

Another way:

Function myFunc(arg) As String
    myFunc = Application.ThisCell.Formula
    myFunc = Mid(myFunc, InStr(myFunc, "(") + 1)
    myFunc = Left(myFunc, InStrRev(myFunc, ")") - 1)
End Function

=MyFunc(Max(A5:A10)) returns the string "Max(A5:A10)"