First Published 6 June 2023


This code uses Visual Basic Extensibility to list all procedures in each code module (form/report/standard/class) to the Immediate window

It builds upon code originally published by the late Chip Pearson in his excellent article Programming the VBA Editor

NOTE: Chip Pearson's code was originally intended for use in Excel but I have adapted it for use in Access (see below)

The code uses early binding and requires the VBA reference Microsoft Visual Basic for Applications Extensibility 5.3



Save the code to a standard module such as modVBE

CODE:

'This code lists all the procedures in all modules to the immediate window
'It requires the VBA reference Microsoft Visual Basic for Applications Extensibility 5.3
'Colin Riddington - Mendip Data Systems 27/03/2019

Sub ListAllModuleProcedures()

On Error GoTo Err_Handler

      Dim VBAEditor As VBIDE.VBE
      Dim vbProj As VBIDE.VBProject
      Dim vbComp As VBIDE.VBComponent
      Dim CodeMod As VBIDE.CodeModule
      Dim lineNum As Long
      Dim NumLines As Long
      Dim ProcName As String
      Dim ProcKind As VBIDE.vbext_ProcKind

      Set VBAEditor = Application.VBE
      Set vbProj = VBAEditor.ActiveVBProject

      For Each vbComp In vbProj.VBComponents
           Set CodeMod = vbComp.CodeModule

           Debug.Print vbComp.Name      'display module name

           With CodeMod
                 lineNum = .CountOfDeclarationLines + 1
                 Do Until lineNum >= .CountOfLines
                       ProcName = .ProcOfLine(lineNum, ProcKind)
                       lineNum = .ProcStartLine(ProcName, ProcKind) + _
                              .ProcCountLines(ProcName, ProcKind) + 1

                        Debug.Print " - " & ProcName      'display procedure name

                  Loop
            End With
      Next vbComp

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err.number & " in ListAllModuleProcedures : " & Err.description
      GoTo Exit_Handler

End Sub

Example Output:

Form_frmQuerySQL
        - CheckSQL
        - cmdClose_Click
        - cmdCopy_Click
        - cmdRestore_Click
        - cmdSave_Click
        - Form_Load
        - txtSQL_AfterUpdate
Form_frmRelationshipDetail
        - cmdClose_Click
        - cmdPrint_Click
        - Form_Load
Form_fsubObjectsInfo
Form_fsubQueryInfo
        - cmdDesign_Click
        - cmdSQL_Click
        - Form_GotFocus
        - Form_Load
Report_rptDependencyInfo
        - Report_NoData
        - Report_Load
Report_rptRelationshipInfo
        - Report_NoData
        - Report_Load
modControlState
        - ShowControls
        - LockControls
        - EnableControls
modFunctions
        - FormattedMsgBox
        - IsObjectLoaded
        - HideNavigationPane
        - MinimizeNavigationPane
        - MaximizeNavigationPane
        - ShowNavigationPane
        - GetProgramName
        - GetVersion
        - GetVersionDate
        - GetCopyright
        - GetWebsite
        - GetLatestDBAnalyzed

NOTE:  
The above code could be modified to:
a)   use late binding so the the reference Microsoft Visual Basic for Applications Extensibility 5.3 is no longer required
b)   save the list of module procedures to a text file or to a table

If you just want to list all the procedures in a specified code module, then use the following code:

Sub ListProcedures(strModule As String)

'This code will list all the procedures in the named module
'For example ListProcedures("modVBECode")
'Colin Riddington

On Error GoTo Err_Handler

     Dim VBAEditor As VBIDE.VBE
     Dim vbProj As VBIDE.VBProject
     Dim vbComp As VBIDE.VBComponent
     Dim CodeMod As VBIDE.CodeModule
     Dim lineNum As Long
     Dim NumLines As Long
     Dim ProcName As String
     Dim ProcKind As VBIDE.vbext_ProcKind

     Set VBAEditor = Application.VBE
     Set vbProj = VBAEditor.ActiveVBProject
     Set vbComp = vbProj.VBComponents(strModule)
     Set CodeMod = vbComp.CodeModule

     With CodeMod
           lineNum = .CountOfDeclarationLines + 1
           Do Until lineNum >= .CountOfLines
                 ProcName = .ProcOfLine(lineNum, ProcKind)
                 lineNum = .ProcStartLine(ProcName, ProcKind) + _
                       .ProcCountLines(ProcName, ProcKind) + 1
                  Debug.Print ProcName ', .CountOfLines, .CountOfDeclarationLines
           Loop
     End With

Exit_Handler:
     Exit Sub

Err_Handler:
     If Err = 9 Then
           MsgBox "Module does not exist", vbCritical, "No such module"
     Else
           MsgBox "Error " & Err.number & " in ListProcedures : " & Err.description
     End If
     GoTo Exit_Handler

End Sub

Download

You can download the above code together with many other related procedures. These are mostly taken from Chip Pearson's article and modified for use in Access

Click to download:       ModVBE      (.bas text file - zipped)

Unzip the file then import direct into the VBE using File . . . Import File



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 6 June 2023

Return to Code Samples Page

Return to Top