如何在Excel VBA中获取给定模块名称的Function和Sub列表

I am working on a helper macro that look into the list function on a given module name on the active excel workbook. Ex: I have a module name "Module1". Inside this module has the following function or sub

我正在研究一个辅助宏，它调查活动excel工作簿上给定模块名称的list函数。例如：我有一个模块名称“Module1”。该模块内部具有以下功能或子功能

Sub Sub1()
End Sub

Sub Sub2()
End Sub

Function Func1()
End Function

Function Func2()
End Function

Is there a command or routine that can return the list of Function and Sub names?

是否有可以返回功能和子名称列表的命令或例程？

2 个解决方案

#1

Here is a link to Chip Pearson's site. This is where I go whenever I need to program something that affects or uses the VBE. There are 2 sections that might interest you. One will list all modules in a project. And another will list all procedures in a module. Hope that helps.

这是Chip Pearson网站的链接。每当我需要编写影响或使用VBE的东西时，这就是我去的地方。您可能会对两个部分感兴趣。一个将列出项目中的所有模块。另一个将列出模块中的所有过程。希望有所帮助。

http://www.cpearson.com/excel/vbe.aspx

Code from the site (make sure to visit the site for instructions on adding a reference to the VBIDE object library:

来自站点的代码（请确保访问该站点以获取有关添加对VBIDE对象库的引用的说明：

This code will list all the procedures in Module1, beginning the listing in cell A1.

此代码将列出Module1中的所有过程，从单元格A1开始列出。

Sub ListProcedures()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    Dim NumLines As Long
    Dim WS As Worksheet
    Dim Rng As Range
    Dim ProcName As String
    Dim ProcKind As VBIDE.vbext_ProcKind

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule

    Set WS = ActiveWorkbook.Worksheets("Sheet1")
    Set Rng = WS.Range("A1")
    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
            ProcName = .ProcOfLine(LineNum, ProcKind)
            Rng.Value = ProcName
            Rng(1, 2).Value = ProcKindString(ProcKind)
            LineNum = .ProcStartLine(ProcName, ProcKind) + _
                    .ProcCountLines(ProcName, ProcKind) + 1
            Set Rng = Rng(2, 1)
        Loop
    End With

End Sub

Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
    Select Case ProcKind
        Case vbext_pk_Get
            ProcKindString = "Property Get"
        Case vbext_pk_Let
            ProcKindString = "Property Let"
        Case vbext_pk_Set
            ProcKindString = "Property Set"
        Case vbext_pk_Proc
            ProcKindString = "Sub Or Function"
        Case Else
            ProcKindString = "Unknown Type: " & CStr(ProcKind)
    End Select
End Function

#2

There is also a free tool called "MZ-Tools". Install it as an add-in, It numbers your lines o fcode, generate standard error management code, check unused variables, order your functions and sub and ... document your code, by automatically generating a list of your procedures with parameters, comments, etc.... A great tool!

还有一个名为“MZ-Tools”的免费工具。将它作为插件安装，它为你的行编号，生成标准的错误管理代码，检查未使用的变量，命令你的函数和子文件......记录你的代码，通过参数，注释自动生成你的程序列表等等....一个很棒的工具！

#1