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

[JugglerJAF]

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.

 

[Joe4]

Why not just add a Message Box at the end of that procedure like this?

MsgBox [COLOR=#333333]"The macro 'CheckDescriptionColumn' has finished running."[/COLOR]

 

[JugglerJAF]

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]

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]

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:

MsgBox Application.VBE.ActiveCodePane.CodeModule & " has completed running"

 

[Joe4]

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]

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.

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