VBA Code To Retrieve Current Running Macro Name

[jochryem]

Hi,

I'm trying to find VBA code that will tell me the name of the current macro that is running.

Example: Sub MyMacroName()

Dim MacroName As String

MacroName = HowDoIGetMyMacroName
MsgBox MacroName

End Example:

Does anyone know if code exists to do this?

 

[Joe4]

Welcome to the Board!

I believe that you can only run one macro at a time, so I don't think something like this could work.

Why not just but Message Boxes at the beginning/end of your macros that same something like?
"Macro1 has started running"
"Macro1 is finished running"

Or perhaps look at using a Status Bar (if you search the forum or do a Google Search, there are many articles on how to do these).

 

[jochryem]

I'm trying to use the macro name to join as a part of another sub procedure so that I can duplicate the procedure across multiple modules and workbooks so that I don't have to hard-code the call to the sub procedure name each time as it requires it to be unique

 

[Joe4]

so that I can duplicate the procedure across multiple modules and workbooks

Why would you need to duplicate it across multiple modules and workbooks?
Any procedure you write can be written to run across multiple worksheets and/or workbooks.

 

[wideboydixon]

I don't believe that's possible. Why do you need to know? There may be other solutions ...

WBD

 

[jochryem]

I have code that updates multiple reports from a SQL server (ie: Report A, Report B, Report C and so on). I have a module that is unique for each report and I call sub procedures within each module (a save as macro). There is a ton going on in these modules so I'm trying to simplify it so that it is easier to duplicate (less maintenance) as I have hundreds of reports that get pulled from the SQL server.

 

[Joe4]

Here is another option that may work for you that I have seen people do.
Create a log file, and have each macro write to the log file, writing the name of the Macro running and the date/time it started.
Then, you can refer to the log file at anytime to see where it is in the process.

 

[wideboydixon]

You could have a global variable in a module:

Public currentMacroName As String

Then set it at the start of each macro as appropriate:

Public Sub MyMacro()

currentMacroName = "MyMacro"

...

End Sub

WBD