Newton Excel Bach, not (just) an Excel Blog

… and other procedures.

I wanted to make a list of all the VBA procedures in a workbook, and discovered that Excel doesn’t have a built-in way of doing that.  People have written code to work their way around that of course, and the code I ended up using came from Jon Peltier’s site: http://peltiertech.com/WordPress/list-vba-procedures-by-vba-module-and-vb-procedure/

Jon had modified some code, that originally sent the list to a dialog, so that it pasted it into a new workbook instead.  Nothing wrong with that, but it seemed to me more convenient to have it operate as a User Defined Function (UDF), returning an array, so that you could have your list wherever you wanted.  A couple of other changes, while I was at it:

• I split the output into two columns, module name and procedure name.
• I started off creating a large array for the output, rather than re-diming for every new line.

Here’s the end result: (Or download GetProcedures.xlsb)

Function GetProcedures()
'' Based on:
'' Displaying a List of All VBA Procedures in an Excel 2007 Workbook
''     from the Ribbon (June 2009)
'' by Frank Rice, Microsoft Corporation
'' http://msdn.microsoft.com/en-us/library/dd890502(office.11).aspx#
'' as modified by Jon Peltier
'' http://peltiertech.com/WordPress/list-vba-procedures-by-vba-module-and-vb-procedure/

'' Modified by Doug Jenkins to operate as a UDF and return results as a 2 column array

'' set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library

' Declare variables to access the Excel workbook.
Dim app As Excel.Application
Dim wb As Excel.Workbook

Dim sOutput() As String
Dim sFileName As String

' Declare variables to access the macros in the workbook.
Dim vbProj As VBIDE.VBProject
Dim vbComp As VBIDE.VBComponent
Dim vbMod As VBIDE.CodeModule

    ' Declare other miscellaneous variables.
Dim iRow As Long
Dim iCol As Long
Dim iLine As Integer
Dim sProcName As String
Dim pk As vbext_ProcKind
' Add constant for output array size to avoid redims
Const OutRows As Long = 10000

    Set app = Excel.Application

'For Each wb In app.Workbooks
For Each vbProj In app.VBE.VBProjects

' Get the project details in the workbook.
        On Error Resume Next
sFileName = vbProj.FileName
If Err.Number <> 0 Then sFileName = "file not saved"
        On Error GoTo 0

        ' initialize output array
ReDim sOutput(1 To OutRows, 1 To 2)
sOutput(1, 1) = sFileName
sOutput(2, 1) = vbProj.Name
iRow = 0

        ' check for protected project
        On Error Resume Next
Set vbComp = vbProj.VBComponents(1)
        On Error GoTo 0

If Not vbComp Is Nothing Then
            ' Iterate through each component in the project.
For Each vbComp In vbProj.VBComponents

                ' Find the code module for the project.
Set vbMod = vbComp.CodeModule

                ' Scan through the code module, looking for procedures.
iLine = 1
Do While iLine < vbMod.CountOfLines
sProcName = vbMod.ProcOfLine(iLine, pk)
If sProcName <> "" Then
iRow = iRow + 1
'   ReDim Preserve sOutput(1 To 2 + iRow)
sOutput(2 + iRow, 1) = vbComp.Name
sOutput(2 + iRow, 2) = sProcName
iLine = iLine + vbMod.ProcCountLines(sProcName, pk)
                    Else
                        ' This line has no procedure, so go to the next line.
iLine = iLine + 1
                    End If
                Loop

                ' clean up
Set vbMod = Nothing
Set vbComp = Nothing

            Next
        Else
' ReDim Preserve sOutput(1 To 3, 1)
sOutput(3, 1) = "Project protected"
        End If

If iRow = 0 Then    ' UBound(sOutput) = 2 Then
' ReDim Preserve sOutput(1 To 3)
sOutput(3, 1) = "No code in project"
        End If
sOutput(2, 2) = iRow

GetProcedures = sOutput

        ' clean up
Set vbProj = Nothing
    Next

End Function

And here’s what the output looks like:

GetProcedures() UDF Output