如何用VBA及VBA扩展性库获取Excel中被调用的VBA过程列表

前置准备 #

• 启用Microsoft Visual Basic for Applications Extensibility 5.3引用：打开VBA编辑器，点击工具→引用，勾选该选项。
• 开启VBA项目对象模型访问权限：打开Excel选项→信任中心→信任中心设置→宏设置，勾选信任对VBA项目对象模型的访问。

核心实现代码 #

Sub GetProcedureDependencies()
    Dim vbProj As VBIDE.VBProject
    Dim vbComp As VBIDE.VBComponent
    Dim vbMod As VBIDE.CodeModule
    Dim procName As String
    Dim startLine As Long, endLine As Long
    Dim lineText As String
    Dim calledProc As String
    Dim calledModule As String
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim i As Long
    
    ' 创建/定位结果工作表
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("调用依赖分析")
    If Err.Number <> 0 Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = "调用依赖分析"
    End If
    On Error GoTo 0
    
    ' 写入表头
    ws.Cells(1, 1).Value = "调用模块"
    ws.Cells(1, 2).Value = "调用过程"
    ws.Cells(1, 3).Value = "被调用过程"
    ws.Cells(1, 4).Value = "被调用过程所在模块"
    rowNum = 2
    
    Set vbProj = ThisWorkbook.VBProject
    
    ' 遍历所有标准模块
    For Each vbComp In vbProj.VBComponents
        If vbComp.Type = vbext_ct_StdModule Then
            Set vbMod = vbComp.CodeModule
            
            startLine = 1
            ' 遍历模块内所有过程
            Do While startLine < vbMod.CountOfLines
                procName = vbMod.ProcOfLine(startLine, vbext_pk_Proc)
                If procName <> "" Then
                    endLine = vbMod.ProcStartLine(procName, vbext_pk_Proc) + vbMod.ProcCountLines(procName, vbext_pk_Proc) - 1
                    
                    ' 遍历过程内每一行代码
                    For i = startLine To endLine
                        lineText = Trim(vbMod.Lines(i, 1))
                        ' 跳过注释和空行
                        If Left(lineText, 1) <> "'" And lineText <> "" Then
                            calledProc = ExtractCalledProcedure(lineText)
                            If calledProc <> "" Then
                                calledModule = FindProcedureModule(vbProj, calledProc)
                                If calledModule <> "" Then
                                    ' 写入结果
                                    ws.Cells(rowNum, 1).Value = vbComp.Name
                                    ws.Cells(rowNum, 2).Value = procName
                                    ws.Cells(rowNum, 3).Value = calledProc
                                    ws.Cells(rowNum, 4).Value = calledModule
                                    rowNum = rowNum + 1
                                End If
                            End If
                        End If
                    Next i
                    
                    startLine = endLine + 1
                Else
                    startLine = startLine + 1
                End If
            Loop
        End If
    Next vbComp
    
    ' 格式化结果表格
    ws.Range("A1:D1").Font.Bold = True
    ws.UsedRange.Columns.AutoFit
    MsgBox "依赖分析完成，结果已写入工作表：" & ws.Name, vbInformation
End Sub

' 提取行内调用的过程名
Private Function ExtractCalledProcedure(lineText As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    ' 匹配Call 过程( 或直接过程(的格式
    regex.Pattern = "^(?:Call\s+)?([A-Za-z0-9_]+)\("
    regex.IgnoreCase = True
    
    Dim matches As Object
    Set matches = regex.Execute(lineText)
    If matches.Count > 0 Then
        ExtractCalledProcedure = matches(0).SubMatches(0)
    Else
        ExtractCalledProcedure = ""
    End If
End Function

' 查找过程所在的标准模块
Private Function FindProcedureModule(vbProj As VBIDE.VBProject, procName As String) As String
    Dim vbComp As VBIDE.VBComponent
    Dim vbMod As VBIDE.CodeModule
    Dim startLine As Long
    
    For Each vbComp In vbProj.VBComponents
        If vbComp.Type = vbext_ct_StdModule Then
            Set vbMod = vbComp.CodeModule
            startLine = 1
            Do While startLine < vbMod.CountOfLines
                If vbMod.ProcOfLine(startLine, vbext_pk_Proc) = procName Then
                    FindProcedureModule = vbComp.Name
                    Exit Function
                End If
                startLine = startLine + 1
            Loop
        End If
    Next vbComp
    
    FindProcedureModule = ""
End Function

代码说明 #

• 遍历当前工作簿所有标准模块，分析每个过程的调用语句，提取被调用过程并匹配其所属模块。
• ExtractCalledProcedure函数用正则表达式匹配常见的过程调用格式，若你的代码有特殊调用写法（如带对象前缀、嵌套括号参数），可修改正则表达式的Pattern来适配。
• FindProcedureModule当前仅查找标准模块，若需要覆盖类模块、窗体模块，可修改vbComp.Type的判断条件，添加vbext_ct_ClassModule、vbext_ct_MSForm等类型。
• 结果自动写入调用依赖分析工作表，表头加粗并自动调整列宽。

示例输出表格 #

内容的提问来源于stack exchange，提问作者emerald77