Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts

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

    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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    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 website contains 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

  4. The Following 2 Users Say Thank You to Gary Frieder For This Useful Post:

    Jensina (2011-11-07),jweissmn1 (2011-08-19)

  5. #3
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts
    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

  6. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    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

  7. #5
    New Lounger
    Join Date
    Nov 2011
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thank you Gary!

    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

  8. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Glad if that helped.

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

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •