Function to make a list of functions …

… 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:

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
'' as modified by Jon Peltier

'' 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)
                        ' This line has no procedure, so go to the next line.
iLine = iLine + 1
                    End If

                ' clean up
Set vbMod = Nothing
Set vbComp = Nothing

' 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

End Function

And here’s what the output looks like:

GetProcedures() UDF Output

GetProcedures() UDF Output

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

3 Responses to Function to make a list of functions …

  1. jp says:

    Doug … I get a debug with code … do you have a sample file … cheers, James


  2. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s