VBA to get the module name

[jjzhang]

Does anyone know how to get the current module name using VBA in a sub. Thanks. -JJ

 

[RoryA]

If you mean the module containing the code that is running, then you can't. If you mean the currently active module in the VBEditor, then:

application.VBE.ActiveCodePane.CodeModule.Name

 

[jjzhang]

If you mean the module containing the code that is running, then you can't. If you mean the currently active module in the VBEditor, then:

application.VBE.ActiveCodePane.CodeModule.Name

Rory:

It works, I run a sub with the code you gave and it print the module name. Thanks.

By the way, what's the difference the module containing the code that is running and the currently active module in the VBEditor, if I don't run the sub in the module how I use the code to get the name. But if I run the sub, then the module becomes the module containing the code that is running. I am confused. -JJ

 

[Norie]

Why do you want the module name anyway?

 

[jjzhang]

Why do you want the module name anyway?

Yes, this may be a bad design, too many modules out there, and the main sub calls other subs in different modules. Just want to keep a record where the sub are.

 

[RoryA]

You can run a sub without it's codemodule being active in the VBE. if you are hoping to use this in an error handler for example, it won't work.

 

[Tom Urtis]

If you mean the module containing the code that is running, then you can't. If you mean the currently active module ...

Rory, a question - -

When I establish a reference to the MS VBA Extensibility 5.3 Library and the Excel and Office 11.0 (or your version) Object Library

and

stick this code in a module

Sub ModuleNameTest()
Run "MyMacroInfo"
End Sub
 
Private Sub MyMacroInfo()
Dim MacroName$, SubName$, ModArr As Variant
Dim ModName As Object, strModName$, i&, j&
MacroName = ActiveSheet.Buttons(Application.Caller).OnAction
SubName = Application.Replace(MacroName, 1, Application.Search("!", MacroName), "")
ModArr = Array(0, 1, 2, 3)
For Each ModName In ActiveWorkbook.VBProject.VBComponents
For j = LBound(ModArr) To UBound(ModArr)
i = 0
On Error Resume Next
i = ModName.CodeModule.ProcStartLine(SubName, CLng(ModArr(j)))
Err.Clear
If i > 0 Then
strModName = ModName.Name
Exit For
End If
Next j
Next ModName
MsgBox _
"Full macro name:" & vbCrLf & MacroName & vbCrLf & vbCrLf & _
"Actual subroutine name:" & vbCrLf & SubName & vbCrLf & vbCrLf & _
"Module name where " & SubName & " is housed: " & vbCrLf & strModName, _
64, "Information about macro being run..."
End Sub

and

assign the ModuleNameTest to a Forms button on any worksheet

and

I insert a few other modules and make sure one of them is active other than the one I stuck the code in, by selecting some code in it or somehow making it be as "actived" as I can

and

I click the Forms button to run the ModuleNameTest macro

then

I get a message box telling me the name of the macro I ran and the module it is in.


Is this different from what you said in terms of how a module is "active".

 

[jjzhang]

Tom:

Got it, thank you.

Best regards,

jj

 

[RoryA]

Tom,
By 'active module', I meant the one that is active in the VBE, not (necessarily) the one in which any current code is running. Your code works to get the module in which the code is running, as long it is called form a control. Otherwise you would have to hardcode the procedure name, in which case you might as well add the module name.
I was assuming that this was the age old question of an easy way to log errors and which module/routine they occurred in.

 

[TJTOLBE1]

Just wondering if this has been fixed in 2010, I have multi users on different sites that use a excel workbooks. On open I want to check if its the current version of the workbook [userform, module etc] or not.