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.
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.
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
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.
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.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
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:
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.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
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.
Tom Urtis
Hey, Tom, great reply for a first timer! Looks like what I want. I'll check it out. Thanx for the reply!
Hi Tom
Hope You're doing well and it's good to see You here at VBAX.
Kind regards,
Dennis
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables