Code to return name of macro that's just been run

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
265
Say I have a macro called: Sub CheckDescriptionColumn()

What I'd like to do is to add some code at the end of the macro to display a message box advising that the macro has finished running, but I need the message box to return the name of the macro, so something along the lines of "The macro 'CheckDescriptionColumn' has finished running."

I've tried using Sub.Name but that's not valid code.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
Why not just add a Message Box at the end of that procedure like this?
Code:
MsgBox [COLOR=#333333]"The macro 'CheckDescriptionColumn' has finished running."[/COLOR]
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
265
I can do that, but I was hoping for an automatic method, as I have more than 40 different macros to set up and I thought it would be easier to have 1 line that could reference the Sub name without me having to specify the individual name in each piece of code.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
Unfortunately, that doesn't appear to be possible, without some workarounds.
See: https://stackoverflow.com/questions/31376519/get-name-of-current-procedure-in-vba
and: https://stackoverflow.com/questions/3792134/get-name-of-current-vba-function

I suppose you could create a Global Variable and set it at the beginning with the name of the Macro is running, but then that really doesn't save you any work (it actually creates more work).
Since you already have to identify and edit these 40 macros anyway, copying and pasting the procedure name in that MsgBox line of code really should only be a little more work than what you were planning on doing.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
What you want is possible provided the name of the module you are running is the subroutine name. For example, in your VBE Project if the sub CheckDescriptionColumn is Module 5 (as an example), go to the VBE menu View>Properties Window and change the name from Module 5 to CheckDescriptionColumn. Then at the end of your subroutine place this line of code:
Code:
MsgBox Application.VBE.ActiveCodePane.CodeModule & " has completed running"
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
What you want is possible provided the name of the module you are running is the subroutine name. For example, in your VBE Project if the sub CheckDescriptionColumn is Module 5 (as an example), go to the VBE menu View>Properties Window and change the name from Module 5 to CheckDescriptionColumn. Then at the end of your subroutine place this line of code:
Interesting alternative...

So, if they have 40 different procedures, that would require them to put each procedure in its own Module (by itself), and rename each Module the same name as the procedure.
If they set it up that way initially, that would work. But if not, it is probably more work to do all that than to simply add the hard-coded message box (it seems like they want to do it with the least amount of effort as possible).
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
Interesting alternative...

So, if they have 40 different procedures, that would require them to put each procedure in its own Module (by itself), and rename each Module the same name as the procedure.
If they set it up that way initially, that would work. But if not, it is probably more work to do all that than to simply add the hard-coded message box (it seems like they want to do it with the least amount of effort as possible).
No, I wouldn't put each procedure in a separate module. Rather, I'd group related Procedures within modules in the customary way, then give the modules a relevant name. Here's an example of three procedures in a module Named: "AreaOfRectangle". Copy the code below and rename the module to AreaOfRectangle, then run it. Try canceling one of the input boxes to see what happens.
Code:
Dim L, W

Sub Procedure1()
L = InputBox("Enter the Length of a rectangle")
If L = "" Then
    MsgBox "You have exited " & Application.VBE.ActiveCodePane.CodeModule & " prior to completion"
    Exit Sub
End If
Call Procedure2
End Sub
Sub Procedure2()
W = InputBox("Enter the Width of a rectangle")
If W = "" Then
    MsgBox "You have exited " & Application.VBE.ActiveCodePane.CodeModule & " prior to completion"
    Exit Sub
End If
Call Procedure3
End Sub
Sub Procedure3()
MsgBox "Area of rectangle is " & L * W & " units" & vbCrLf & vbCrLf & _
Application.VBE.ActiveCodePane.CodeModule & " has completed running"

End Sub
 
Last edited:

Forum statistics

Threads
1,089,226
Messages
5,406,972
Members
403,115
Latest member
LateOne

This Week's Hot Topics

Top