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

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
297
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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).
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top