Hi Allen
You need to Reference the "Microsoft Visual Basic for Applications Extensibility 5.?"
library. Using this you can access the modules (ThisWorkbook, Worksheets, standard
Modules, Class Modules and Forms).
This code will list all Module, Procedure Name, Procedure Type and the Code for each
procedure:
Public Sub ListAll()
Dim vbp As VBProject
Dim vbc As VBComponent
Dim colProcItem As Collection
Dim piItem As ProcInfo
Set vbp = ActiveWorkbook.VBProject
' Iterate all components (modules)
For Each vbc In vbp.VBComponents
' Create collection of ProcInfo objects (one per procedure)
Set colProcItem = GetVBProcedureNames(vbc.CodeModule)
For Each piItem In colProcItem
' Module name, procedure name
With piItem
Debug.Print .Module, .ProcName, .ProcType
' Now display the code
''' Debug.Print .ProcCode
End With
Next piItem
Next vbc
End Sub ' ListAll
Private Function GetVBProcedureNames(ByVal modAny As CodeModule) As Collection
Dim lngCLines As Long
Dim lngType As Long
Dim colProcNames As New Collection
Dim strProcName As String
Dim piItem As ProcInfo
' Loop through modules collection looking for valid procedure names
With modAny
' Loop through all code lines, looking for a procedure
For lngCLines = 1 To .CountOfLines
' Get the name of the procedure on the current line
strProcName = .ProcOfLine(lngCLines, lngType)
' If non-blank then we've found a proc
If LenB(strProcName) > 0 Then
' Collect all of the relevant procedure information
Set piItem = New ProcInfo
piItem.Module = modAny.Name
piItem.ProcName = strProcName
piItem.ProcType = lngType
piItem.ProcCode = modAny.Lines(lngCLines, _
.ProcCountLines(strProcName, lngType))
' Skip the code lines by adding the number of lines in the proc (less one)
' to the current line number
lngCLines = lngCLines + .ProcCountLines(strProcName, lngType) - 1
' Save the procedure information
colProcNames.Add piItem
End If
Next lngCLines
End With
Set GetVBProcedureNames = colProcNames
End Function ' GetVBProcedureNames
Add this to a Class Module named "ProcInfo"
<=========Start of Class Module ProcInfo==========>
Public Module As String
Public ProcName As String
Public ProcType As String
Public ProcCode As String
<=========End of Class Module ProcInfo===========>
The above is an Excel VBA example, but will port readily.
HTH + Cheers - Peter
