Getting the Name of Executing Macro

[Cyberdude]

Does VBA have a way for an executing macro to fetch its own name? On all my Msgbox and Inputbox commands I like to specify a title that (ideally) looks like this:

'workbookname' (macroname)

I have found this to be very handy.

[Jacob Hilderbrand]

You can assign the name as a constant and then use it however you want.

Option Explicit

Sub Macro1()
Const MyName As String = "Macro1"
MsgBox MyName
End Sub

[Cyberdude]

Yes, I've done that. I would still like a command that will fetch it for me. That way I don't have to type out the macro name. I can build a model title that is macroname-independent, then copy it whenever I need it. Always trying to cut out the routine stuff.

[Ken Puls]

Hi there,

Colo's website has a thread discussing this issue. He has a suggestion for dealing with it, but the discussion is still ongoing for exactly what you are looking for.

[Tom Urtis]

A lazy Sunday morning and my first post on this board...I hope it comes out looking right, sorry if not.

You asked:
"Does VBA have a way for an executing macro to fetch its own name?"

The short answer is Yes, below is a longer explanation of one approach to the issue, which tested fine for me using Excel2003.

Since there are only a few ways a macro can be called, let's pick a common way, that being the act of clicking a drawing object such as a Forms button, to which is attached the macro you want to know information about. In this case we can use Application.Caller to identify the OnAction property, and take it from there.

First, establish a reference to the MS VBA Extensibility 5.3 Library and the Excel and Office 11.0 (or your version) Object Library, and assign any macro to a Forms button (you've probably already done that).

Next, stick this macro named "MyMacroInfo" in a standard VBA module:

Private Sub MyMacroInfo()
Dim MacroName$, SubName$, ModArr As Variant
Dim ModName As Object, strModName$, i&, j&
MacroName = ActiveSheet.Buttons(Application.Caller).OnAction
SubName = Application.Replace(MacroName, 1, Application.Search("!", MacroName), "")
ModArr = Array(0, 1, 2, 3)
For Each ModName In ActiveWorkbook.VBProject.VBComponents
For j = LBound(ModArr) To UBound(ModArr)
i = 0
On Error Resume Next
i = ModName.CodeModule.ProcStartLine(SubName, CLng(ModArr(j)))
Err.Clear
If i > 0 Then
strModName = ModName.Name
Exit For
End If
Next j
Next ModName
MsgBox _
"Full macro name:" & vbCrLf & MacroName & vbCrLf & vbCrLf & _
"Actual subroutine name:" & vbCrLf & SubName & vbCrLf & vbCrLf & _
"Module name where " & SubName & " is housed: " & vbCrLf & strModName, _
64, "Information about macro being run..."
End Sub

In the above code, the Message Box is only for displaying information to demonstrate the example. Presumably you really want to use the variables to tell you whatever you want to know about the actual macro of interest while said macro of interest is being executed.

Example, let's say you have a macro named "Test" assigned to a Forms button. Usually you click the Forms button to run the "Test" macro. Now in the "Test" macro, insert the line
Run "MyMacroInfo"
and click the button as usual to run the "Test" macro again. This time, a Message Box will appear, telling you the full macro name for "Test", including its parent workbook, its actual sub name, and the name of the module where it is housed, if you care about such things.

If you are calling the macro some other way, such as by a Call or Run statement from another macro, then that is even easier, since you'd be the workbook author and could insert a customized statement in that macro to identify the nested macro name. If you are calling the macro from a keyboard shortcut instead of clicking a Forms button, then intercept that keystroke with an OnKey statement in the workbook Activate and Deactivate events.

Here with the common Forms button click, a simple Run statement should do, for whatever actual macro assigned to a Forms button that you want to know information about. This support macro (which I've named "MyMacroInfo") tells you about that "Test" macro (or any other macro into which you insert the Run statement), which is the subject of your question as I understand it.

[Cyberdude]

Hey, Tom, great reply for a first timer! Looks like what I want. I'll check it out. Thanx for the reply!

[XL-Dennis]

Hi Tom

Hope You're doing well and it's good to see You here at VBAX.

Kind regards,
Dennis

[Zack Barresse]

Well Hi Tom! Very nice to see you here! Your expertise is quite welcome! (Cyberdude, pay attention to Tom, he's a foremost expert in VBA! VERY talented!!!)

Cyberdude: The Application.Caller method is good, but beware when testing or stepping through your code. You MUST call from the Object associated with it and either use the Stop command or Breakpoints (the latter being my preferred method) to step into your code. This can also be used for UDF's and such, example here.