About Us

Alan P

Chip<

Thanks, that worked great.

Alan

"Chip Pearson" wrote:

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)

On Thu, 27 Aug 2009 11:28:02 -0700, Alan P
wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy sheets from source wkbk to another wkbk	Diddy	Excel Programming	0	August 21st 08 01:48 PM
Find WkBk Path, Then use this path to open another WkBk in Subfold	dim	Excel Programming	10	April 23rd 08 01:00 AM
Creating a Hyperlink from 1 WKBK to open another WKBK and activate	Barb Reinhardt	Excel Programming	10	May 31st 07 06:45 PM
Remove/disable event macros in SavedAs wkbk?	Ed	Excel Programming	5	March 22nd 05 01:42 PM
Convert XL2K wkbk w/ macros to XL97?	Ed[_9_]	Excel Programming	1	February 13th 04 08:46 PM

Menu

About Us