Results 1 to 7 of 7

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,806
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Why not just add a Message Box at the end of that procedure like this?
    Code:
    MsgBox "The macro 'CheckDescriptionColumn' has finished running."
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,806
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Unfortunately, that doesn't appear to be possible, without some workarounds.
    See: https://stackoverflow.com/questions/...ocedure-in-vba
    and: https://stackoverflow.com/questions/...t-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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,495
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

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

    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"
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,806
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,495
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by Joe4 View Post
    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 by JoeMo; May 18th, 2018 at 11:57 AM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •