[VBA] How to Extract a List of Macro Names from a Module Using Regular Expressions

Introduction

As a VBA project grows larger, you might find yourself thinking, “I forgot what macros I wrote in this module,” or “I need a list of procedure names to create documentation.”

By combining the VBA VBProject object with Regular Expressions (VBScript.RegExp), it is possible to extract the names of all Sub and Function procedures written in a specific module and list them on a worksheet.

In this article, I will explain an advanced and practical technique: reading VBA code as text and using Regular Expressions to automatically generate a list of macro names.

[Important] Prerequisites

To run this macro, you must configure the following two settings beforehand:

1. Enable Access to the VBA Project:
   • Go to File > Options > Trust Center > Trust Center Settings > Macro Settings.
   • Check the box for “Trust access to the VBA project object model”.
2. Add References:
   • In the VBA Editor (VBE), go to Tools > References.
   • Check “Microsoft VBScript Regular Expressions 5.5”.
   • Check “Microsoft Visual Basic for Applications Extensibility 5.3” (Required to use VBIDE objects).

VBA Sample Code

This macro targets a standard module named “MyMacros.” It extracts the names and types of all Subs and Functions and writes them into columns A and B of the “MacroList” sheet.

' Create a list of procedures (Sub/Function) in a specific module
Sub CreateProcedureList()
    Const TARGET_MODULE_NAME As String = "MyMacros" ' The name of the module to search
    
    Dim vbc As VBIDE.VBComponent
    Dim code As VBIDE.CodeModule
    Dim codeText As String
    Dim regex As VBScript_RegExp.RegExp
    Dim matches As VBScript_RegExp.MatchCollection
    Dim match As VBScript_RegExp.Match
    Dim outputSheet As Worksheet
    Dim rowNum As Long
    
    '--- 1. Get the module code as text ---
    On Error Resume Next
    Set vbc = ThisWorkbook.VBProject.VBComponents(TARGET_MODULE_NAME)
    On Error GoTo 0
    
    If vbc Is Nothing Then
        MsgBox "Module '" & TARGET_MODULE_NAME & "' not found.", vbCritical
        Exit Sub
    End If
    
    Set code = vbc.CodeModule
    ' Read all lines from line 1 to the last line
    codeText = code.Lines(1, code.CountOfLines)
    
    '--- 2. Search for Sub/Function lines using Regex ---
    Set regex = New VBScript_RegExp.RegExp
    With regex
        .Global = True      ' Search the entire string
        .MultiLine = True   ' Multiline mode
        ' Pattern: Starts with (Public/Private/Sub/Function) and captures the name
        .Pattern = "^(Public |Private )?(Sub|Function)\s+([a-zA-Z0-9_]+)\("
    End With
    
    Set matches = regex.Execute(codeText)
    
    '--- 3. Write results to the output sheet ---
    Set outputSheet = ThisWorkbook.Worksheets("MacroList")
    outputSheet.Cells.ClearContents
    outputSheet.Range("A1:B1").Value = Array("Type", "Procedure Name")
    rowNum = 2
    
    For Each match In matches
        ' SubMatches(1) is "Sub" or "Function"
        outputSheet.Cells(rowNum, "A").Value = match.SubMatches(1)
        ' SubMatches(2) is the Procedure Name
        outputSheet.Cells(rowNum, "B").Value = match.SubMatches(2)
        rowNum = rowNum + 1
    Next match
    
    MsgBox "Macro list creation complete."

End Sub

Code Explanation

1. Retrieving Module Code

Set code = vbc.CodeModule
codeText = code.Lines(1, code.CountOfLines)

• We access the code written in the module via the .CodeModule property of the VBComponent.
• The .Lines property retrieves the code as a single long string, from line 1 to the last line (CountOfLines).

2. Searching with Regular Expressions

.Pattern = "^(Public |Private )?(Sub|Function)\s+([a-zA-Z0-9_]+)\("

This is the search pattern used:

• ^: Start of the line.
• (Public |Private )?: Matches “Public ” or “Private ” if they exist (optional).
• (Sub|Function): Matches the word “Sub” or “Function”.
• \s+: Matches one or more spaces.
• ([a-zA-Z0-9_]+): Matches the procedure name (alphanumeric characters and underscores). This is the part we want to extract.
• \(: Matches the opening parenthesis that follows the name.

3. Writing the Results

outputSheet.Cells(rowNum, "A").Value = match.SubMatches(1)
outputSheet.Cells(rowNum, "B").Value = match.SubMatches(2)

• SubMatches returns the strings matched by the parts enclosed in () in the pattern.
• SubMatches(1) corresponds to the second group (Sub|Function), giving us the type.
• SubMatches(2) corresponds to the third group ([a-zA-Z0-9_]+), giving us the actual procedure name.

Summary

In this article, I explained an advanced technique using the VBA CodeModule object and Regular Expressions to automatically list macro definitions.

• You can access the code text using VBProject.VBComponents("...").CodeModule.
• Regular Expressions allow you to efficiently find specific patterns (like Sub/Function definition lines) within the code.

By applying this method, you can build tools to semi-automate the creation of macro documentation or check if coding standards are being followed, significantly improving your VBA development productivity.