We help IT Professionals succeed at work.

Listing of Excel VBA Modules and Sub and Function Procedures in a workbook

How to loop though each module/sheet object in a Excel\VBA project and within each module and object(sheet) list the module name and all Sub/Function names?
Regards
Brian
Comment
Watch Question

Top Expert 2010

Commented:
Hello canesbr,

I adapted some of the code found at Excel MVP Chip Pearson's site (http://www.cpearson.com/excel/vbe.aspx)
to do this.  It returns all the subs/functions present in the VB Project of the active workbook.

** This code requires a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library!!

Regards,

Patrick
Sub ListModules()
    
    Dim wb As Workbook
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim TheCode As String
    Dim DestR As Long
    Dim MemCount As Long
    Dim arr As Variant
    Dim Member As String
    Dim Visibility As String
    
    Set wb = ActiveWorkbook
    Set VBProj = wb.VBProject
    
    Workbooks.Add
    [a1:f1] = Array("Workbook", "Component Name", "Component Type", "Member Name", "Member Type", "Visibility")
    DestR = 1
    
    For Each VBComp In VBProj.VBComponents
        Set CodeMod = VBComp.CodeModule
        TheCode = CodeMod.Lines(1, CodeMod.CountOfLines)
        arr = RegExpFind(TheCode, "((Public|Private|Friend) )?(Function|Sub) [\w-]+(?=\()")
        If IsArray(arr) Then
            For MemCount = 0 To UBound(arr)
                Member = arr(MemCount)
                DestR = DestR + 1
                Cells(DestR, 1) = wb.Name
                Cells(DestR, 2) = VBComp.Name
                Cells(DestR, 3) = ComponentTypeToString(VBComp.Type)
                Cells(DestR, 4) = Mid(Member, InStrRev(Member, " ") + 1)
                Cells(DestR, 5) = IIf(InStr(1, Member, " sub ") > 0, "Sub", "Function")
                Visibility = Left(Member, InStr(1, Member, " ") - 1)
                Select Case Visibility
                    Case "Public", "Private", "Friend"
                        Cells(DestR, 6) = Visibility
                    Case Else
                        Cells(DestR, 6) = "Public"
                End Select
            Next
        End If
    Next
    
    Columns.AutoFit
    
End Sub


Function ComponentTypeToString(ComponentType As VBIDE.vbext_ComponentType) As String
    Select Case ComponentType
        Case vbext_ct_ActiveXDesigner
            ComponentTypeToString = "ActiveX Designer"
        Case vbext_ct_ClassModule
            ComponentTypeToString = "Class Module"
        Case vbext_ct_Document
            ComponentTypeToString = "Document Module"
        Case vbext_ct_MSForm
            ComponentTypeToString = "UserForm"
        Case vbext_ct_StdModule
            ComponentTypeToString = "Code Module"
        Case Else
            ComponentTypeToString = "Unknown Type: " & CStr(ComponentType)
    End Select
End Function

Open in new window

Top Expert 2010

Commented:
canesbr,

You'll probably also have to allow access to the VB Project.  That page I link to above explains how to do that.

Patrick

Author

Commented:
Hi Patrick
Thanks...
(a) I tried to run this and got a compile error "Sub or function not defined" on RegExpFind
(b) I could get the Tools References to work ok to add the MSVB Extensibility but I first tried to put the module in to Personal.xls but the Tools>References is greyed out there. When the ListModules is working, can it reside in personal? I now have it in a workbook.

I have Excel 2007 on Vista.

Regards
Brian
Top Expert 2011

Commented:
You could also try the following which is also from Chip's most excellent site.  ListModules is the sub ato call, and it inserts a new sheet into the active workbook with the details from the same workbook code modules.

Chris
Sub ListModules()
Dim VBProj As Object
Dim VBComp As Variant
Dim ws As Worksheet
Dim rng As Range
    
    Set VBProj = ActiveWorkbook.VBProject
    Set ws = ActiveWorkbook.Worksheets.Add
    Set rng = ws.Range("A1")
    
    For Each VBComp In VBProj.VBComponents
        rng(1, 1).Value = VBComp.Name
        rng(1, 2).Value = ComponentTypeToString(VBComp.Type)
        ListProcedures VBComp.Name, rng
        Set rng = rng(2, 1)
    Next VBComp
    ws.Range("a1:d1").EntireColumn.AutoFit
End Sub


Function ComponentTypeToString(ComponentType As Variant) As String
Const vbext_ct_ActiveXDesigner As Integer = 11
Const vbext_ct_ClassModule As Integer = 2
Const vbext_ct_Document As Integer = 100
Const vbext_ct_MSForm As Integer = 3
Const vbext_ct_StdModule As Integer = 1
    
    Select Case ComponentType
        Case vbext_ct_ActiveXDesigner
            ComponentTypeToString = "ActiveX Designer"
        Case vbext_ct_ClassModule
            ComponentTypeToString = "Class Module"
        Case vbext_ct_Document
            ComponentTypeToString = "Document Module"
        Case vbext_ct_MSForm
            ComponentTypeToString = "UserForm"
        Case vbext_ct_StdModule
            ComponentTypeToString = "Code Module"
        Case Else
            ComponentTypeToString = "Unknown Type: " & CStr(ComponentType)
    End Select
End Function

Sub ListProcedures(strModule As String, rng As Range)
Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim LineNum As Long
Dim NumLines As Long
Dim ProcName As String
Dim ProcKind As Long
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strModule)
    Set CodeMod = VBComp.CodeModule
    
    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
            ProcName = .ProcOfLine(LineNum, ProcKind)
            rng(1, 3).Value = ProcKindString(ProcKind)
            rng(1, 4).Value = ProcName
            LineNum = .ProcStartLine(ProcName, ProcKind) + _
                    .ProcCountLines(ProcName, ProcKind) + 1
            Set rng = rng(2, 1)
        Loop
    End With
End Sub


Function ProcKindString(ProcKind As Long) As String
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    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

Open in new window

Top Expert 2010

Commented:
canesbr,

My apologies.  You also need the code below for my suggestion to work.

Patrick

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.Test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Open in new window

Author

Commented:
Hi Patrick
I now get Run Time error '-2137024809 (800 70057)': Invalid procedure call or argument
in
Sub ListModules
...
TheCode = CodeMod.Lines(1, CodeMod.CountOfLines)
...

It had done an Excel Object which had a Worksheet function and was looking at another which had no code at all when it got the error. So I added "        If CodeMod.CountOfLines > 0 Then " which seemed to solve that problem.
So now it lists the Component Name with say "Sheet7" but if it has been renamed, is there a way to get "Sheet7(MyName)" listed?
And the Member Type says "Function" even if it is a "Sub" - Is there a way to deal with that?
(iii) In Tools>References I had Personal checked. But the objects in there were not listed?
Regards
Brian
Top Expert 2011

Commented:
Did you have any problems with my code?

Chris

Author

Commented:
Hi Chris
So I got it to work - it creates a sheet in the active workbook with a listing, and another sheet with the objects in Personal which I had checked in Tools:References. Is that expected - 2 sheets?
It also lists the SheetX and not Sheetx(Rename)
The other routine lists the workbook name and whether Private/Public
Regards
Brian

Author

Commented:
Hi Chris:
FYI
Here is the changed code
I put in the WB loop and I put in the Application.Calculation coding. (If the workbook takes a whailes to calculate, writing the lists into a worksheet takes for ever without the calculationManual. It is generalized code.
Regards
Brian
Sub ListModules()
Dim VBProj As Object
Dim VBComp As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim rng As Range
SaveAppCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
        For Each wb In Application.Workbooks
            Set VBProj = wb.VBProject
            Set ws = ActiveWorkbook.Worksheets.Add
            Set rng = ws.Range("A1")
            For Each VBComp In VBProj.VBComponents
                rng(1, 1).Value = VBComp.Name
                rng(1, 2).Value = ComponentTypeToString(VBComp.Type)
                ListProcedures wb, VBComp.Name, rng
                Set rng = rng(2, 1)
            Next VBComp
            ws.Range("a1:d1").EntireColumn.AutoFit
        Next wb
    Application.Calculation = SaveAppCalc
End Sub

Function ComponentTypeToString(ComponentType As Variant) As String
      Const vbext_ct_StdModule As Integer = 1
    Const vbext_ct_ClassModule As Integer = 2
         Const vbext_ct_MSForm As Integer = 3
Const vbext_ct_ActiveXDesigner As Integer = 11
       Const vbext_ct_Document As Integer = 100
    Select Case ComponentType
        Case vbext_ct_ActiveXDesigner:ComponentTypeToString = "ActiveX Designer"
        Case vbext_ct_ClassModule:ComponentTypeToString = "Class Module"
        Case vbext_ct_Document:ComponentTypeToString = "Document Module"
        Case vbext_ct_MSForm:ComponentTypeToString = "UserForm"
        Case vbext_ct_StdModule:ComponentTypeToString = "Code Module"
        Case Else:ComponentTypeToString = "Unknown Type: " & CStr(ComponentType)
    End Select
End Function

Sub ListProcedures(wb As Workbook, strModule As String, rng As Range)
Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim LineNum As Long
Dim NumLines As Long
Dim ProcName As String
Dim ProcKind As Long
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    Set VBProj = wb.VBProject 'ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strModule)
    Set CodeMod = VBComp.CodeModule
    
    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
                   ProcName = .ProcOfLine(LineNum, ProcKind)
            rng(1, 3).Value = ProcKindString(ProcKind)
            rng(1, 4).Value = ProcName
                    LineNum = .ProcStartLine(ProcName, ProcKind) + .ProcCountLines(ProcName, ProcKind) + 1
            Set rng = rng(2, 1)
        Loop
    End With
End Sub

Function ProcKindString(ProcKind As Long) As String
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    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 '

Open in new window

Top Expert 2011

Commented:
It actually only creates the one new sheet each time it's run ... perhaps the others were from the run with PAtricks code?

Speed wasn't an issue for me but application.screenupdating = false at the start and application.screenupdating = true at the end may also help.

Chris
Top Expert 2011

Commented:
The following adds private/public if that is required.

Chris
Sub ListModules()
Dim VBProj As Object
Dim VBComp As Variant
Dim ws As Worksheet
Dim rng As Range
    
    Set VBProj = ActiveWorkbook.VBProject
    Set ws = ActiveWorkbook.Worksheets.Add
    Set rng = ws.Range("A1")
    
    Application.ScreenUpdating = False
    For Each VBComp In VBProj.VBComponents
        rng(1, 1).Value = VBComp.Name
        rng(1, 2).Value = ComponentTypeToString(VBComp.Type)
        ListProcedures VBComp.Name, rng
        Set rng = rng(2, 1)
    Next VBComp
    ws.Range("a1:d1").EntireColumn.AutoFit
    Application.ScreenUpdating = True

End Sub


Function ComponentTypeToString(ComponentType As Variant) As String
Const vbext_ct_ActiveXDesigner As Integer = 11
Const vbext_ct_ClassModule As Integer = 2
Const vbext_ct_Document As Integer = 100
Const vbext_ct_MSForm As Integer = 3
Const vbext_ct_StdModule As Integer = 1
    
    Select Case ComponentType
        Case vbext_ct_ActiveXDesigner
            ComponentTypeToString = "ActiveX Designer"
        Case vbext_ct_ClassModule
            ComponentTypeToString = "Class Module"
        Case vbext_ct_Document
            ComponentTypeToString = "Document Module"
        Case vbext_ct_MSForm
            ComponentTypeToString = "UserForm"
        Case vbext_ct_StdModule
            ComponentTypeToString = "Code Module"
        Case Else
            ComponentTypeToString = "Unknown Type: " & CStr(ComponentType)
    End Select
End Function

Sub ListProcedures(strModule As String, rng As Range)
Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim LineNum As Long
Dim NumLines As Long
Dim ProcName As String
Dim ProcKind As Long
Dim strScope As String
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strModule)
    Set CodeMod = VBComp.CodeModule
    
    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
            ProcName = .ProcOfLine(LineNum, ProcKind)
                strScope = Trim(.Lines(LineNum, 1))
                If StrComp(Left(.Lines(.ProcBodyLine(ProcName, ProcKind), 1), Len("Public")), "Public", vbBinaryCompare) = 0 Then
                    strScope = "Public"
                ElseIf StrComp(Left(.Lines(.ProcBodyLine(ProcName, ProcKind), 1), Len("Private")), "Private", vbBinaryCompare) = 0 Then
                    strScope = "Private"
                ElseIf StrComp(Left(.Lines(.ProcBodyLine(ProcName, ProcKind), 1), Len("Friend")), "Friend", vbBinaryCompare) = 0 Then
                    strScope = "Friend"
                Else
                    strScope = "Default"
                End If
            rng(1, 3).Value = strScope
            rng(1, 4).Value = ProcKindString(ProcKind)
            rng(1, 5).Value = ProcName
            LineNum = .ProcStartLine(ProcName, ProcKind) + _
                    .ProcCountLines(ProcName, ProcKind) + 1
            Set rng = rng(2, 1)
        Loop
    End With
    If rng(0, 3).Text = "" Then Set rng = rng(2, 1)
End Sub


Function ProcKindString(ProcKind As Long) As String
Const vbext_pk_Proc As Long = 0
Const vbext_pk_let As Long = 1
Const vbext_pk_Set As Long = 2
Const vbext_pk_Get As Long = 3
    
    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

Open in new window

Top Expert 2010

Commented:
canesbr,

My apologies for any confusion. Please download the attached sample file, and then run the sub ListModules.
It will list all procedures in all modules of the active workbook. The relevant code is in the modules Module1
and sai_RegExpFind.

Chris: this question has inspired me to get to work on an add-in that will take Chip's fine work and take it a
couple of steps further. When I have it prototyped, I'd be pleased as punch if you could help me kick the
tires.

Patrick
Q-26254303.xlsm
Top Expert 2011
Commented:

Happy to!

Chris

Author

Commented:
Great
For your consideration: If you could include:
Loop all open workbooks
Loop all connected Projects (Personal, PatternFills)
Show Procedure (Sub, Function, Property (Get, Set))
Show Visibility (Private, Friend, Public)
Show Module (Code, Form, Document, Class, ..)
For Document Module show Sheet# (Sheet Name)
Show Line Counts (Declaration, Code, Comments?)
Output Option - (new workbook, new sheet in workbooks)
Thanq
Regards
Brian

PS Can I leave this open until you post the result? Then I can give points etc.?
Top Expert 2010
Commented:
Brian,

With respect, that is a pretty significant piece of work, and I doubt I will have the prototype ready for at least a
week, and after that I will be leaning on the heavy hitters here like Chris, rorya, zorvek, etc to kick the tires and
help me work out the bugs.

The finished product will go into an article when it's ready for prime time.  I promise that when the article is
published, I will return here and post a link to it.

In the meantime, I will posit that Chris and I have answered this question as originally posed.

Patrick

Author

Commented:
Consolidated article to be posted.

Author

Commented:
Patrick
Tried to open Q-26254303.xlsm (62 KB)
It contained xml - which I have not used to date.
I tried to open it.
Not sure what to open - no VBA code appears.
Regards
Brian
Top Expert 2010

Commented:
canesbr,

Which version of Excel are you using?

Patrick

Author

Commented:
2007
Top Expert 2010

Commented:
canesbr,

How odd.  That should have worked.

Did you try downloading it first to your computer and then opening it from Excel, or did you try to open it
directly from the link?  (If you haven't tried the former, please do.)

Patrick

Author

Commented:
Hi
I downloaded it and it was a zip.
I extracted all from the zip and got

C:\Users\Owner\Downloads\Q-26254303.xlsm\docProps
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl
C:\Users\Owner\Downloads\Q-26254303.xlsm\[Content_Types].xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\_rels
C:\Users\Owner\Downloads\Q-26254303.xlsm\docProps\app.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\docProps\core.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\sharedStrings.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\styles.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\theme
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\vbaProject.bin
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\workbook.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\worksheets
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\_rels
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\theme\theme1.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\worksheets\sheet1.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\worksheets\sheet2.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\worksheets\sheet3.xml
C:\Users\Owner\Downloads\Q-26254303.xlsm\xl\_rels\workbook.xml.rels
C:\Users\Owner\Downloads\Q-26254303.xlsm\_rels\.rels

Now what?
Regards
Brian
Top Expert 2010

Commented:
As promised, I posted an up to date version of the file to my blog:
http://www.experts-exchange.com/blogs/matthewspatrick/B_2536-Documenting-VBA-Projects-in-Excel.html

Patrick

Author

Commented:
Initial Use of Patrick-Documenter - worked.
COMMENTS:
I guess I was hoping for at least the Sheet (Name) from my list of featurenality.
The References do not show "Personal.xls" which was checked on for the selected workbook. There was a blank entry in the output, so I suspect that a reference was found and that it was "personal" but the field shown in the output was null.
Would be good to have password protected projects show up in the workbook list in the LearnForm as say "IntellectualProperty.xlsm (Locked)". I suppose one could go and open selected ones, but it would be good if one were selected, for the routine to try open it and the user be presented with the password dialog.
My Personal.xls is password protected, so that may be another reason for the blank Reference in the output.
Also would be good to have some output option. Currently all the workbooks results go into Book1!Sheet1 so if one had the option to say have each book go into separate sheets? Or each workbook being analyzed to have a new sheet?
Also for consideration: Have an optional comments column in the output which would just list lines that were comments (not lines that have a statement ending with a comment). Since the objective is documentation, this featurenality would be big time useful.
Then it would be desirable to show if a  DocumentModule was Hidden or Deeply Hidden.
And last, but not least to surround the code with the below since ListProcedures has zero interest in recalculating the sheet every time it stores an item for output. Saves a boatload of time for heavy duty workbooks.
    Dim SaveCalculation
    SaveCalculation = Application.Calculation
    Application.Calculation = xlCalculationManual
'ListProcedures Code
    Application.Calculation = SaveCalculation

Regards
Brian