Thanks, joeu2004 - works just as I wan it to :-)


CE



Den 10.05.2013 17:47, joeu2004 skrev:
"Charlotte E." wrote:
I may have asked my question the wrong way - sorry
- English is not my native language...

Your English is just fine. Your original question was perfectly clear,
to wit:

"Is it possible to test, if a function (UDF) is called from a worksheet
(as a normal worksheet function) or from another macro/VBA code?"


"Charlotte E." wrote:
What I meant was:
Say, I call my function from a worksheet the usual fasion:
=MyFunction(Some argument)
The function will always return False
...but, if I call the function from another macro, like:
MyVar = MyFunction(Some argument)
The function will return what ever result the function might calculate.

You might put the following code at the beginning of the function:

Dim s as String
On Error Resume Next
s = Application.Caller.Address
On Error GoTo 0
If Len(s) = 0 Then MyFunction = False: Exit Function

However, MyFunction will return False if it is called from a "macro"
(subroutine) that is called from another function that was called from a
worksheet. For example, =MyOtherFunction(), where MyOtherFunction calls
MySub (Sub MySub), which calls MyFunction.

If you do not want MyFunction to return False in that case, I do not
know of a way to distinguish such an indirect call from the direct call
=MyFunction().

PS: It is not necessary to go to the .Address property. I prefer that
for debug and other purposes. But the following is more efficient:

Dim r as Range
On Error Resume Next
Set r = Application.Caller
On Error GoTo 0
If r Is Nothing Then MyFunction = False: Exit Function

It is also not necessary to use On Error GoTo 0. It is just "good
practice" to disable error trapping when you no longer need it.
Otherwise, other unintended errors might go undiscovered.