Word 2003 VBA: Extracting a list of macros in a module or project

Topic

New Reply

I may be missing something obvious, but is there a way to extract a list of the macros in a module?

I know that I can see the list dynamically in various places, but I need to put the list in a document.

Thanks,

Jessica

Reply | Quote

Replies

Jessica,

There are a number of examples of code to do this around, but the ones I could find all are designed to work with Excel.

One example: This thread from 2001 is close to what you need – except it’s designed for Excel, not Word – look for the ‘PrintCode.xls’ attachment in the third post.
Note that in order for it to run, you’ll need to go to your macro security settings and put a checkmark next to “Trust access to the VBA project object model” (this setting is best left unchecked for normal usage).

Also, this page from Chip Pearson’s websitecontains a bunch of code procedures for working with the VBE Editor.
Chip generously put his code in the public domain, so here’s a version of his code, reworked for Word:

Code:

Sub ListWordProcedures()
   ‘Adapted from original code for Excel from Chip Pearson
   ‘original source: http://www.cpearson.com/excel/vbe.aspx
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Dim NumLines As Long
        Dim Doc As Document
        Dim Para As Paragraph
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind
        
        Set VBProj = ActiveDocument.VBProject
        Set VBComp = VBProj.VBComponents(“Module1″)
        Set CodeMod = VBComp.CodeModule
        
        Set Doc = ActiveDocument
        With CodeMod
            LineNum = .CountOfDeclarationLines + 1
            Do Until LineNum >= .CountOfLines
                ProcName = .ProcOfLine(LineNum, ProcKind)
                Set Para = Doc.Range.Paragraphs.Add
                Para.Range.Text = ProcName & ” – ” & ProcKindString(ProcKind) & vbCrLf
                LineNum = .ProcStartLine(ProcName, ProcKind) + _
                        .ProcCountLines(ProcName, ProcKind) + 1
            Loop
        End With
        Set Para = Nothing
        Set Doc = Nothing
    End Sub
Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
        Select Case ProcKind
            Case vbext_pk_Get
                ProcKindString = “Property Get”
            Case vbext_pk_Let
                ProcKindString = “Property Let”
            Case vbext_pk_Set
                ProcKindString = “Property Set”
            Case vbext_pk_Proc
                ProcKindString = “Sub Or Function”
            Case Else
                ProcKindString = “Unknown Type: ” & CStr(ProcKind)
        End Select
End Function

Notes:

[*]For this code to run, you need to trust access to the VBA project object model, as described above.
[*]Also, in whatever document you place this code, you need to go to the VB Editor, go to Tools > References, and set a reference to “Microsoft Visual Basic for Applications Extensibility 5.3”.
[*]This code is hard-coded to list procedures in ‘Module 1″ – to list procedures in other modules, you’d need to change the name of the module referred to in the code.
[*]The code is designed to list procedures in Module 1 in the currently active document. That means that to use it, you’d either have to (1) insert this code into the document that contains the code you want to list, or (2) put this code into a global add-in so that you can then call it from the document which contains the code you want to list.

Gary

Reply | Quote

Thanks, Gary – especially for modifying it to work with Word, which is beyond my current VBA skills. I wish the VBA editor had more convenience features, including an easy way to copy stuff from module to module. Of course it is possiible to do it manually by copying the code chunks, but that seems primitive.

– Jessica

Reply | Quote

Glad if that helped.

It is possible to copy entire modules by dragging them from one project to another in the project explorer, but as for specific chunks of code, I’ve never done anything but copy/paste – am not expecting that to ever change in the VBE!

Gary

Reply | Quote

Thank you Gary!! You made this tedious task easy for me to complete quickly!
Code adapted from above and added to loop through an entire directory to document all modules in all templates in one directory.

Code:

Sub DirLoop()
‘Adapted from MSDN KB Article 139724
‘Source: http://support.microsoft.com/kb/139724

        Dim MyFile As String, Sep As String
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Dim NumLines As Long
        Dim Doc As Document
        Dim Para As Paragraph
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind
        Dim Title As String
        Dim Para1 As Paragraph
        Dim Para2 As Paragraph

      ‘ Sets up the variable “MyFile” to be each file in the directory
      ‘ This example looks for all the files that have an .xls extension.
      ‘ This can be changed to whatever extension is needed. Also, this
      ‘ macro searches the current directory. This can be changed to any
      ‘ directory.
        Sep = Application.PathSeparator

      ‘ Test for Windows or Macintosh platform. Make the directory request. (I have windows so I cut out the Mac code found in the KB)

      ‘ Starts the loop, which will continue until there are no more files
      ‘ found.
        MyFile = Dir(“C:yourpathgoeshere” & Sep & “*.dotm”) ‘our macros are in word template documents
      Do While MyFile  “”

         ‘Adapted from original code for Excel from Chip Pearson
         ‘original source: http://www.cpearson.com/excel/vbe.aspx
         
         ‘Original adaptation by Gary Friedson
         ‘Source: http://windowssecrets.com
         
        Set VBProj = ActiveDocument.VBProject
        ‘Deletes file extension for use below in naming the resulting document
        Title = Left(MyFile, Len(MyFile) – 5)
        Set Doc = New Document ‘modified here to create a new document for each documentation of each template
        
        For Each VBComp In VBProj.VBComponents
            Set Para2 = Doc.Range.Paragraphs.Add
            Para2.Range.Text = vbCrLf
            Set CodeMod = VBComp.CodeModule
            Set Para1 = Doc.Range.Paragraphs.Add
            Para1.Range.Text = CodeMod & vbCrLf
            With CodeMod
                LineNum = .CountOfDeclarationLines + 1
                Do Until LineNum >= .CountOfLines
                    ProcName = .ProcOfLine(LineNum, ProcKind)
                    Set Para = Doc.Range.Paragraphs.Add
                    ‘I opted not to have the extra verbiage Gary
                    ‘had here and added a function to make the list easier to read (code above)
                    Para.Range.Text = ProcName & vbCrLf
                    LineNum = .ProcStartLine(ProcName, ProcKind) + _
                            .ProcCountLines(ProcName, ProcKind) + 1
                Loop
            
            End With
        Next VBComp
            Doc.SaveAs (“C:yoursavetofilepath” & Title & “.docx”)
            Doc.Close
            Set Para = Nothing
            Set Doc = Nothing
            MsgBox CurDir() & Sep & MyFile
            MyFile = Dir()
        Loop

   End Sub
Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
        Select Case ProcKind
            Case vbext_pk_Get
                ProcKindString = “Property Get”
            Case vbext_pk_Let
                ProcKindString = “Property Let”
            Case vbext_pk_Set
                ProcKindString = “Property Set”
            Case vbext_pk_Proc
                ProcKindString = “Sub Or Function”
            Case Else
                ProcKindString = “Unknown Type: ” & CStr(ProcKind)
        End Select
End Function

Reply | Quote

Glad if that helped.

Welcome to the Lounge! – and thanks for posting your code.

Gary

Reply | Quote