ID module name

[AndreT]

When I use msgbox, I like to display which module this message comes from. Currently I hard code the module name in the message. Can anyone show me is there a system variable that has this module name so I can display it automatically?

Thanks for your advice in advance.

 

[pbaldy]

Check out CodeContextObject in VBA help and see if it does what you want.

 

[vbaInet]

The only way is to create your own like so:

Option Compare Database
Option Explicit

Const MOD_NAME As String = "The Name of the Module"

Public Function SomeFunction()
      Msgbox "Module name is " & MOD_NAME
End Sub

But I'm sure you know that for form and report objects you can use Me.Name to get the name of the object.

Edit: Just noticed your post Paul. I was going to recommend the CodeContextObject but that only works for Form and Report objects. I'm thinking the OP wants the names of Standard Modules as well.

 

[AndreT]

Thank you both for the advices. I checked codecontextobject and tested its .name property, it display the form name the Sub is from, just like vbaInet said, it works the same as me.name. I guess I'll just have to hard code it.

 

[gemma-the-husky]

we had a similar thread on this recently, and the conclusion was that vba doesn't expose the procedure/function name for use internally.

 

[vbaInet]

I think the OP is to do with getting the module name, not the name of the sub or function.

The name of the sub/function can actually be retrieved but the function requires a reference to that function and the line number within any part of the function.

You can get the module name using this:

Application.VBE.SelectedVBComponent.Name

But that's a rather long line of code when it can be done more succintly with your own declaration.

 

[AndreT]

I think the OP is to do with getting the module name, not the name of the sub or function.

The name of the sub/function can actually be retrieved but the function requires a reference to that function and the line number within any part of the function.

You can get the module name using this:

Application.VBE.SelectedVBComponent.Name

But that's a rather long line of code when it can be done more succintly with your own declaration.

Wow! That's just what I asked for. I'll give it a try and let you know the result. Any special library I need to include? Thanks for your help.

Actually I want to have both module name and sub/function/class name so I can code a generic msgbox response and copy it around. additional advice on that will definitely make my day even better.

 

[vbaInet]

Nope!

If you know the line number where the function occured you will be able to get the sub or function name. I have a written a little function that will get you the function name and module name if you supply the line number. For form and report objects you only need to supply the line number but for Standard Modules and Classes it also requires the name of the module. So using the ideas from posts #2, #3 (only Standard Modules and Classes require the CONST line) and #6 the function is:

Public mdlName As String, procName As String


Public Function GetFuncModule(lineNum As Long, Optional basName As String)
On Error GoTo Err_H
    
    Dim obj As Object, objType As String
    
    Set obj = CodeContextObject
    
    objType = Nz(DLookup("[Type]", "MSysObjects", "[Name] = '" & obj.Name & "'"), vbNullString)

Err_IsNotObject:
    
    Select Case objType
        Case -32768
            mdlName = "Form_" & obj.Name
            procName = Application.VBE.ActiveVBProject.VBComponents(mdlName).CodeModule.ProcOfLine(lineNum, vbext_pk_Proc)
            
        Case -32764
            mdlName = "Report_" & obj.Name
            procName = Application.VBE.ActiveVBProject.VBComponents(mdlName).CodeModule.ProcOfLine(lineNum, vbext_pk_Proc)
            
        Case Else
            ' It's not a form or report object. It's a Standard Module or Class
            mdlName = basName
            procName = Application.VBE.ActiveVBProject.VBComponents(basName).CodeModule.ProcOfLine(lineNum, vbext_pk_Proc)
            
    End Select
    
    MsgBox "Module: " & mdlName & vbNewLine & "Procedure: " & procName

Exit_Err_H:
    Exit Function
    
Err_H:
    If Err.Number = 7955 Then
        GoTo Err_IsNotObject
    Else
        MsgBox Err.Number
        Resume Exit_Err_H
    End If

End Function

So in an error handler you run the function which will then save the values into the public variables, mdlName and procName. For reports and forms, you run it this way:

GetFuncModule [COLOR=Red]TheLineNumberhere[/COLOR]

Standard Modules and Classes like this:

GetFuncModule [COLOR=Red]TheLineNumberhere[COLOR=Blue], MOD_NAME[/COLOR][/COLOR]

Here's a site that contains a tool that you can use to number your code and I think it returns the line number when an error occurs:

http://www.fmsinc.com/MicrosoftAccess/VBACodingTools.html

 

[AndreT]

Thanks so much. I'll give it a try.