Calling macro?

[Charlotte E]

In a macro it is possible to call another macro with the statement 'Call',
like

Call AnotherMacro

But, is it possible in the called macro ("AnotherMacro") to get the name of
the macro, which called "AnotherMacro"?

(Hope, you get catch my drift


TIA,
CE

 

[Rick Rothstein]

In a macro it is possible to call another macro with the

statement 'Call', like

Call AnotherMacro

But, is it possible in the called macro ("AnotherMacro") to
get the name of the macro, which called "AnotherMacro"?

I don't believe so, well, at least not automatically. I believe the name of
subroutines, functions, etc., like variable names, get converted to memory
addresses and bundled into something called a "symbol table", so I do not
believe the names of these survive the compile process unless direct steps
are taken to do so. The steps depend on whether your AnotherMacro is really
a macro or not. Macro are meant to be called from the worksheet in some
manner (press ALT+F8, assign it to a button, etc.) and, as such, cannot have
any arguments. However, if your AnotherMacro will not be called this way,
that is, it will only be called from within other code, then you can give it
an argument and pass the calling routine's name through it. For example,

Sub MainMacro()
....
Call AnotherSubroutine("MainMacro")
....
End Sub

Sub AnotherSubroutine(WhoCalledMe As String)
....
MsgBox "I was called by " & WhoCalledMe
....
End Sub

Another possibility is to use a global variable to transmit the name... this
method can be used with real macros as well as subroutines. Put this line of
code at the top of the Module with your macros in it...

Dim WhoCalledMe As String

then this macro combination will do what you asked...

Sub MainMacro()
....
WhoCalledMe = "MainMacro"
Call AnotherMacro("MainMacro")
....
End Sub

Sub AnotherMacro()
....
MsgBox "I was called by " & WhoCalledMe
....
End Sub


Rick Rothstein (MVP - Excel)

 

[joeu2004]

But, is it possible in the called macro ("AnotherMacro")
to get the name of the macro, which called "AnotherMacro"?

If you put a breakpoint in the called macro, you can press ctrl+L or
click View > Call Stack to see not only who called the current macro,
but the complete history of calls.

(I don't know if there is a limit to the depth of the call stack that
is viewable.)

If your intent is to determine this programmatically -- for example,
to do one thing if the caller is X, but to do another thing if the
caller is Y -- there certain __can__ be a mechanism for doing that, at
least in theory.

But I do not know if VBA provides a method for doing that, or if a
kernel dll provides an API (function) for doing that.

(I have implemented such functions for other languages on other
operation systems, so I know it is doable.)

 

[joeu2004]

I believe the name of subroutines, functions, etc., like
variable names, get converted to memory addresses and
bundled into something called a "symbol table"

Yes. But procedures names especially are usually "external" links,
which means that the symbol table usually provides the mapping between
name and address so that a linker can make the "connection" between
caller and callee either at run time (dynamic linkage) or at
(pseudo)compile time.

Moreover, when there is a symbolic debugger, as in VBA, the symbol
table also provides the mapping between even private global and local
variable names and addresses.

So in theory, VBA could provide a method for accessing at least that
information programmatically. Alternatively, the kernel could provide
an API for doing this.

I have implemented both for compiled languages as well as for
interpretive languages. But I do not know if VBA or the Win kernel
makes this available programmatically.

 

[joeu2004]

if your AnotherMacro [...] it will only be called from
within other code, then you can give it an argument and
pass the calling routine's name through it. [....]
Another possibility is to use a global variable to
transmit the name... this method can be used with real
macros as well as subroutines.

Notwithstanding what I explained regarding symbol tables in general, I
do agree with you that it is probably better to use one of these
mechanisms instead of relying on any programming interface that VBA or
the kernel might provide.

In fact, for most purposes, I would not pass a name at all, except for
debugging/logging purposes. Instead, I would pass a number, which the
called procedure might use to alter its behavior.

One benefit of relying on a number (or arbitrary string) instead of
the caller's name is that procedure names have a habit of changing
over time ;-). Moreover, it allows multiple callers to affect the
same conditional behavior, which should be defined in a "functionally
cohesive" manner independent of a caller's name.