Listing Names of Subs & Functions

[Zack Barresse]

Just fyi, ASAP Utilities now has something similar to this as a built-in function/userform. It doesn't quite function the way this (thread) is going but if you have ASAP, you may want to check it out. It's pretty cool. The path is ...

ASAP Utilities -> System -> Macro/VBA Information (beta)

It's not completed yet, and (personally) I think it could have some more useful items, but the framework is there to give you an idea.

[mdmackillop]

Old and slow: at least I'm not alone!
MD(the grey)

[johnske]

Thanx Zack!!

Just installed the ASAP utilities 308 setup and there's lotsa really cool stuff in there. But yeah, the macro list would be a great advantage when it's operational

[johnske]

Old and slow: at least I'm not alone!
MD(the grey)

Yeah, trubbl is while I'm still tryin to nut out exactly wot someone's dun, someone else submits an improvement on the first, then someone else gives an improvement on the improvement, then someone gives a completely different way with further improvements.... ....way too fast for an old codger like me :rofl

Oh to be twenty years yunger again....

[johnske]

Hi John,

Is this the sort of thing that you mean?

Sub Test()
    '1st part of code
    Function ListProcedures(wbk As Workbook, strModName As String) As String
    'add a reference to VBA Extensibility library <<<<<HERE !!!!
    Dim VBCodeMod As CodeModule
    'last part of code
End Function

A nice reference for this sort of thing is Chip Pearson's page here:
http://www.cpearson.com/excel/vbe.htm

HTH

Thanx for the link too Richie, I didn't have the help file VEENOB3 that was given there (on that site) so I had always wondered how you could programmatically add a reference (like yours above) for ppl that didn't know how to do it. I found it in that help file, haven't tried it yet, so I'm assuming you could put is as the 1st part of a procedure that requires the reference...Reference add/subtract code follows >>>

Private WithEvents X As ReferencesEvents
Sub Test()
    Set X = Application.VBE.Events.ReferencesEvents
End Sub

Private Sub X_ItemAdded(ByVal Reference As VBIDE.Reference)
    ' Put code to support item addition here
End Sub

Private Sub X_ItemRemoved(ByVal Reference As VBIDE.Reference)
    ' Put code to support item removal here
End Sub

[johnske]

Ok, this is a bit of everything, you can now use the listbox to open the VBE window and show the selected module, you can then copy code from the module, import or export modules, but you cant change the code in the module till you unload the userform.

The page to print the list on is deleted and a new one added (with a little formatting now) each time you choose this option.

I left out Matts option of showing whether it's a sub, private sub, function etc as I'll end up with too many columns for it to be practical (basically, for me it's more information than required). Similarly, I haven't included add-ins (you can do that yourself if you want it).

(But this still only looks at the active book)

'This lists all VB components in a 4 column list box

Private Sub UserForm_activate()
    Dim MyList(100, 4) As Variant
    Dim Count As Long, N, Typ As Integer
    Dim VBC As VBComponent, WB As Workbook
    With ListBox1
        .ControlTipText = "Click the module you want (you can " & _
        "look at or copy, but not change the code)"
    End With
    '//headings
    MyList(0, 0) = "COMPONENT NAME"
    MyList(0, 1) = "COMPONENT TYPE"
    MyList(0, 2) = "PROCEDURES"
    MyList(0, 3) = "BOOK NAME"
    '//define list
    N = 1
    For Each VBC In ActiveWorkbook.VBProject.VBcomponents
        MyList(N, 0) = VBC.Name
        Typ = VBC.Type
        If Typ = 1 Then MyList(N, 1) = "Bas Module"
        If Typ = 2 Then MyList(N, 1) = "Cls Module"
        If Typ = 3 Then MyList(N, 1) = "UserForm"
        If Typ = 11 Then MyList(N, 1) = "ActiveX"
        If Typ = 100 Then MyList(N, 1) = "Book/Sheet Cls Module"
        MyList(N, 3) = ActiveWorkbook.Name
        With VBC.CodeModule
            Count = .CountOfDeclarationLines + 1
            Do Until Count >= .CountOfLines
                MyList(N, 2) = .ProcOfLine(Count, vbext_pk_Proc)
                Count = Count + .ProcCountLines(.ProcOfLine(Count, vbext_pk_Proc), vbext_pk_Proc)
                If Count < .CountOfLines Then N = N + 1
            Loop
        End With
        N = N + 1
    Next
    '//load list to listbox
    ListBox1.List = MyList
End Sub

'This opens the VBE window and shows the selected module

Private Sub ListBox1_Click()
    On Error Resume Next '//must select a module
    ThisWorkbook.VBProject.VBcomponents(ListBox1.Value) _
    .CodeModule.CodePane.Show
End Sub
 

'This is optional - used if a 'hard copy' list of components is wanted

Private Sub CommandButton1_Click()
    Dim N As Integer, Count As Long, Typ As Integer
    Dim VBC As VBComponent, WB As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("VB Components").Delete
    Sheets.Add.Name = "VB Components"
    Cells.Select
    Selection.Font.Size = 8
    Rows("1:1").Select
    Selection.Font.Bold = True
    Application.DisplayAlerts = True
    Range("A1") = "COMPONENT NAME"
    Range("B1") = "COMPONENT TYPE"
    Range("C1") = "PROCEDURES"
    Range("D1") = "BOOK NAME"
    N = 2
    For Each VBC In ThisWorkbook.VBProject.VBcomponents
        Range("A" & N) = VBC.Name
        Typ = VBC.Type
        If Typ = 1 Then Range("B" & N) = "Bas Module"
        If Typ = 2 Then Range("B" & N) = "Cls Module"
        If Typ = 3 Then Range("B" & N) = "UserForm"
        If Typ = 11 Then Range("B" & N) = "ActiveX"
        If Typ = 100 Then Range("B" & N) = "Book/Sheet Cls Module"
        Range("D" & N) = ThisWorkbook.Name
        With VBC.CodeModule
            Count = .CountOfDeclarationLines + 1
            Do Until Count >= .CountOfLines
                Range("C" & N) = .ProcOfLine(Count, vbext_pk_Proc)
                Count = Count + .ProcCountLines(.ProcOfLine(Count, vbext_pk_Proc), vbext_pk_Proc)
                If Count < .CountOfLines Then N = N + 1
            Loop
        End With
        N = N + 1
    Next
    Columns.AutoFit
    Range("A1").Select
    Unload Me
End Sub