Hi,


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?


Thanks,

CE

Hi,


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?


Thanks,

CE

Yes and yes! Use it in a cell in the normal fashion, or call it from
VBA. Note that the UDF must be located in a standard module and scoped
public. In the case of a worksheet formula, include the following line
at the top (inside) of your function...

Application.Volatile

...to include it when Excel auto calcs.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hmmmm....

I may have asked my question the wrong way - sorry - English is not my
native language...

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.


CE




Den 10.05.2013 15:50, GS skrev:
Hi,


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?


Thanks,

CE

Yes and yes! Use it in a cell in the normal fashion, or call it from
VBA. Note that the UDF must be located in a standard module and scoped
public. In the case of a worksheet formula, include the following line
at the top (inside) of your function...

Application.Volatile

...to include it when Excel auto calcs.

"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.

PS.... I wrote:
"joeu2004" wrote:
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

I began to wonder why you want this.

If your real intention is simply to prevent MyFunction from being called
from Excel worksheets, perhaps it would be sufficient to make MyFunction
private, to wit:

Private Function MyFunction(...) As Variant

Potential downside: MyFunction can be called only from procedures in the
same module.

I think you could work around that limitation by putting MyFunction into a
class module.

Ahh! You asked if it's possible to test *within* the function itself? I
see joeu2004 has provided a good answer.

Sorry for my misunderstanding, and your english is just fine!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I can elaborate a bit as to how I manage tracking the source of a call.
I use a CallerID variable that I assign each procedure's name to...

Sub MyProc()
Const sSource$ = "MyProc"
....other code
End Sub

Function MyFunc$()
Const sSource$ = "MyFunc()"
....other code
End Function

...where I differentiate between Subs/Functions by including the
parenthesis in the function's CallerID.

This serves severall purposes now but the original intent of
implementing this was for central error handling where an 'error.log'
file is used. More recently I've been using it to determine redirects
in context to user actions initiated through the UI. As a bonus
convenience, it also augmented my 'EnableFastCode' routine so sub
callers wouldn't interupt the current runmode...


At the top of a standard module:
Type udtAppModes
Events As Boolean
CalcMode As Long
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes


Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation =
xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

...where it is called whenever I need improved performance as follows...

EnableFastCode sSource '//turn it on
...do lots of stuff
EnableFastCode sSource, False '//turn it off

...which ensures another process will not disrupt the current state as
the first caller owns the process until done with it, then releases it
so it's available to be owned again by any caller that uses it.
Reasoning is that calls to other processes that also use this may be
initiated by the original caller, or another process called further in
the stack.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks, GS - that was very inspirational - I'll definately take a closer
look at this technique :-)


CE


Den 10.05.2013 19:44, GS skrev:
I can elaborate a bit as to how I manage tracking the source of a call.
I use a CallerID variable that I assign each procedure's name to...

Sub MyProc()
Const sSource$ = "MyProc"
....other code
End Sub

Function MyFunc$()
Const sSource$ = "MyFunc()"
....other code
End Function

...where I differentiate between Subs/Functions by including the
parenthesis in the function's CallerID.

This serves severall purposes now but the original intent of
implementing this was for central error handling where an 'error.log'
file is used. More recently I've been using it to determine redirects in
context to user actions initiated through the UI. As a bonus
convenience, it also augmented my 'EnableFastCode' routine so sub
callers wouldn't interupt the current runmode...


At the top of a standard module:
Type udtAppModes
Events As Boolean
CalcMode As Long
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes


Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

...where it is called whenever I need improved performance as follows...

EnableFastCode sSource '//turn it on
...do lots of stuff
EnableFastCode sSource, False '//turn it off

...which ensures another process will not disrupt the current state as
the first caller owns the process until done with it, then releases it
so it's available to be owned again by any caller that uses it.
Reasoning is that calls to other processes that also use this may be
initiated by the original caller, or another process called further in
the stack.

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.

You're quite welcome!
Enjoy...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion