List or TOC of Macros in Wkbk

[Alan P]

Does anyone know how to create a list/table of contents that includes the
names of all macros in a workbook, and perhaps if there are shortcut keys
assigned?

Thanks,

Alan

 

[Chip Pearson]

The following code is adapted from my code at
http://www.cpearson.com/Excel/VBE.aspx which contains all sorts of
stuff about working programmatically with the VBA editor.

In VBA, go to the Tools menu, choose References, and put a check next
to "Microsoft Visual Basic for Applications Extensibility 5.3".


Sub ListAllVBA()
Dim VBP As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim ListCell As Range
Set ListCell = ActiveSheet.Range("A1")
Set VBP = ActiveWorkbook.VBProject
For Each VBComp In VBP.VBComponents
ListAllProcsInModule VBComp, ListCell
Next VBComp
End Sub

Sub ListAllProcsInModule(VBComp As VBIDE.VBComponent, Rng As Range)
Dim CodeMod As VBIDE.CodeModule
Dim PK As VBIDE.vbext_ProcKind
Dim LineNum As Long
Dim ProcName As String

If VBComp.Type = vbext_ct_ActiveXDesigner Then
Exit Sub
End If
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, PK)
Rng(1, 1).Value = VBComp.Name
Rng(1, 2).Value = ProcName
Rng(1, 3).Value = ProcKindString(PK)
LineNum = .ProcStartLine(ProcName, PK) + _
.ProcCountLines(ProcName, PK) + 1
Set Rng = Rng(2, 1)
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



The sub ListAllVBA will list all procedures in all modules in the
ActiveWorkbook. The list will start in cell A1 of the active sheet.
The first column of the listing is the module name. The second column
is the procedure name, and the third column is the type of procedure.


If you want to list the VBA procs in just one module, use the
following code, which uses the ListAllProcsInModule procedure listed
above. Change the "Module1" to the name of module whose contents you
want to list.

Sub ListOneMod()
Dim VBComp As VBIDE.VBComponent
Dim Rng As Range
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module1")
Set Rng = ActiveSheet.Range("A1")
ListAllProcsInModule VBComp, Rng
End Sub


See http://www.cpearson.com/Excel/VBE.aspx for much more detail.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Alan P]

Chip<

Thanks, that worked great.

Alan

The following code is adapted from my code at
http://www.cpearson.com/Excel/VBE.aspx which contains all sorts of
stuff about working programmatically with the VBA editor.

In VBA, go to the Tools menu, choose References, and put a check next
to "Microsoft Visual Basic for Applications Extensibility 5.3".


Sub ListAllVBA()
Dim VBP As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim ListCell As Range
Set ListCell = ActiveSheet.Range("A1")
Set VBP = ActiveWorkbook.VBProject
For Each VBComp In VBP.VBComponents
ListAllProcsInModule VBComp, ListCell
Next VBComp
End Sub

Sub ListAllProcsInModule(VBComp As VBIDE.VBComponent, Rng As Range)
Dim CodeMod As VBIDE.CodeModule
Dim PK As VBIDE.vbext_ProcKind
Dim LineNum As Long
Dim ProcName As String

If VBComp.Type = vbext_ct_ActiveXDesigner Then
Exit Sub
End If
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, PK)
Rng(1, 1).Value = VBComp.Name
Rng(1, 2).Value = ProcName
Rng(1, 3).Value = ProcKindString(PK)
LineNum = .ProcStartLine(ProcName, PK) + _
.ProcCountLines(ProcName, PK) + 1
Set Rng = Rng(2, 1)
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



The sub ListAllVBA will list all procedures in all modules in the
ActiveWorkbook. The list will start in cell A1 of the active sheet.
The first column of the listing is the module name. The second column
is the procedure name, and the third column is the type of procedure.


If you want to list the VBA procs in just one module, use the
following code, which uses the ListAllProcsInModule procedure listed
above. Change the "Module1" to the name of module whose contents you
want to list.

Sub ListOneMod()
Dim VBComp As VBIDE.VBComponent
Dim Rng As Range
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module1")
Set Rng = ActiveSheet.Range("A1")
ListAllProcsInModule VBComp, Rng
End Sub


See http://www.cpearson.com/Excel/VBE.aspx for much more detail.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)