Albert
Chip Pearson's site is great on this and many related subjects.
http://www.cpearson.com/excel/vbe.aspx
I tweaked his code very slightly to do what you want:
Sub ListProcedures()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim WS As Worksheet
Dim Rng As Range
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule
Set WS = ActiveWorkbook.Worksheets("Sheet1")
Set Rng = WS.Range("A1")
With CodeMod
LineNum = .CountOfDeclarationLines + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Do Until LineNum >= .CountOfLines
' Rng(1, 1).Value = ProcName
' Rng(1, 2).Value = ProcKindString(ProcKind)
Sheet1.ListBox1.AddItem ProcName
Set Rng = Rng(2, 1)
LineNum = LineNum + .ProcCountLines(ProcName,
ProcKind) + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Loop
End With
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
You need to set a reference to the Microsoft Visual Basic for App
Extensibility 5.3, and have a listbox1 on sheet1. His original code
had the component name and type written to the spreadsheet. I don't
know how some of his stuff works, but it sure does.
Ken
