VBA Code Inspector: List All Procedures in Open Workbooks

Overview

Excel VBA Development

When working with multiple workbooks or inherited codebases, getting a quick inventory of all VBA procedures is invaluable. This macro scans every open workbook and lists every Sub, Function, and Property procedure with its component, type, scope, and name.

The companion function fn_Get_VBA_Code goes further, letting you retrieve the full source code of any procedure as a string directly from a worksheet formula, making VBA code itself queryable from the spreadsheet.

How It Works

Cross-Workbook Scanning: Iterates through Application.Workbooks, accessing each VBProject to enumerate all VBComponents (Modules, Classes, UserForms, Sheet modules)
Procedure Enumeration: Uses the CodeModule object to walk through lines, identifying procedure start lines and extracting names, types (Sub/Function/Property), and scope (Public/Private)
Worksheet Function: fn_Get_VBA_Code accepts a component name and procedure name, returning the full source code as text that can be displayed in a cell
Locked Project Handling: Gracefully skips VBProjects that are password-protected, continuing with the next workbook

The VBA Code

Public Sub List_VBA_With_Code()
    Dim Wks_Out As Worksheet
    On Error Resume Next
    Set Wks_Out = ActiveWorkbook.Worksheets("VBA_Code_List_014")
    On Error GoTo 0
    
    If Wks_Out Is Nothing Then
        Set Wks_Out = ActiveWorkbook.Worksheets.Add( _
            After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
        Wks_Out.Name = "VBA_Code_List_014"
    Else
        Wks_Out.Cells.Clear
    End If
    
    Dim Cnt_Row As Long: Cnt_Row = 1
    Wks_Out.Cells(Cnt_Row, 1).Resize(1, 6).Value = _
        Array("Workbook", "Component", "CompType", "ProcType", "Scope", "ProcName")
    Cnt_Row = 2
    
    Dim Wkb_Loop As Workbook
    For Each Wkb_Loop In Application.Workbooks
        Dim Vbp_Proj As Object
        On Error Resume Next
        Set Vbp_Proj = Wkb_Loop.VBProject
        If Err.Number <> 0 Then Err.Clear: GoTo NextWkb
        On Error GoTo 0
        
        Dim Vbc_Cmp As Object
        For Each Vbc_Cmp In Vbp_Proj.VBComponents
            Dim Cmo_Mod As Object: Set Cmo_Mod = Vbc_Cmp.CodeModule
            Dim Num_Lin As Long
            
            For Num_Lin = Cmo_Mod.CountOfDeclarationLines + 1 To Cmo_Mod.CountOfLines
                Dim Enm_Knd As Long
                Dim Str_Nam As String: Str_Nam = Cmo_Mod.ProcOfLine(Num_Lin, Enm_Knd)
                If Len(Str_Nam) = 0 Then GoTo NextLine
                
                Dim Num_Sta As Long: Num_Sta = Cmo_Mod.ProcStartLine(Str_Nam, Enm_Knd)
                If Num_Lin <> Num_Sta Then GoTo NextLine
                
                Wks_Out.Cells(Cnt_Row, 1).Value = Wkb_Loop.Name
                Wks_Out.Cells(Cnt_Row, 2).Value = Vbc_Cmp.Name
                Wks_Out.Cells(Cnt_Row, 3).Value = Vbc_Cmp.Type
                Wks_Out.Cells(Cnt_Row, 4).Value = "Sub/Function"
                Wks_Out.Cells(Cnt_Row, 6).Value = Str_Nam
                Cnt_Row = Cnt_Row + 1
NextLine:
            Next Num_Lin
        Next Vbc_Cmp
NextWkb:
    Next Wkb_Loop
    
    Wks_Out.Columns("A:F").AutoFit
End Sub

Public Function fn_Get_VBA_Code(ByVal Component_Name As String, _
        ByVal Procedure_Name As String) As String
    ' Returns the full source text of a procedure from a VBComponent
    Dim Vbc_Cmp As Object
    On Error Resume Next
    Set Vbc_Cmp = ActiveWorkbook.VBProject.VBComponents(Component_Name)
    On Error GoTo 0
    If Vbc_Cmp Is Nothing Then Exit Function
    
    Dim Cmo_Mod As Object: Set Cmo_Mod = Vbc_Cmp.CodeModule
    Dim Num_Sta As Long, Num_Cnt As Long
    
    On Error Resume Next
    Num_Sta = Cmo_Mod.ProcStartLine(Procedure_Name, 0)
    Num_Cnt = Cmo_Mod.ProcCountLines(Procedure_Name, 0)
    If Err.Number = 0 And Num_Sta > 0 Then
        fn_Get_VBA_Code = Cmo_Mod.Lines(Num_Sta, Num_Cnt)
    End If
    On Error GoTo 0
End Function

Final Thoughts

This inspector turns your VBA codebase into a searchable inventory. Combined with fn_Get_VBA_Code, you can build a self-documenting workbook where the code and its documentation live side by side.