Possible to read Procedure names in if the Microsoft.Vbe.Interop.CodeModule is a Document Module?

Question

A procedure I use can obtain the procedure names that are included in a "vbext_ct_StdModule"
but if the CodeMod is a "vbext_ct_Document" (like "ThisWorkBook" in Excel), then I do not
know the syntax (or even it it is possible) to obtain the names and/or the count of lines
(like CodeMod.ProcCountLines(ProcName, ProcKind))

 Dim VBProj As Microsoft.Vbe.Interop.VBProject
 Dim VBComp As Microsoft.Vbe.Interop.VBComponent
 Dim CodeMod As Microsoft.Vbe.Interop.CodeModule
 Dim ProcKind As Microsoft.Vbe.Interop.vbext_ProcKind
 Dim LineNum as Integer
 Dim Procname as String

 CodeMod = VBComp.CodeModule
 LineNum = 1

 If VBComp.Type is "vbext_ct_StdModule" (or a Standard Module) then
      ProcName = CodeMod.ProcOfLine(LineNum, ProcKind) is Set to an instance of an object
                 and ProcName becomes the name of the first procedure in the CodeMod

 But if VBComp.Type is "vbext_ct_Document" (or a Document Module) then
      ProcName = CodeMod.ProcOfLine(LineNum, ProcKind) is NOT Set to an instance of an object
                 and ProcName becomes "nothing"

Any insight appreciated!
DennisCPA

Answer 1

Yes, ProcOfLine works the same for any kind of module. But note there is not gaurantee that there is a proc on any given line. If the line is white space, module level declarations, "Option" statements, etc. ProcOfLine will return Nothing. So you have to cycle through all the lines in a module and check for the procedure name, if any, at each line.

In some tests I found that the ProcOfLine via interop is a bit buggy and will return the proc name, if any, regardless of the ProcKind supplied. In order to find the actual ProcKind you have to use CountOfProcLines, which will throw an error if the wrong proc kind is supplied.

So, a working example (I tested this is an Excel Workbook 2007 project):

Imports VBE = Microsoft.Vbe.Interop
Imports System.Diagnostics
Module ProcFinder
    Private procKinds() As VBE.vbext_ProcKind = {
            VBE.vbext_ProcKind.vbext_pk_Proc,
            VBE.vbext_ProcKind.vbext_pk_Get,
            VBE.vbext_ProcKind.vbext_pk_Let,
            VBE.vbext_ProcKind.vbext_pk_Set}

        Function FindProcs(ByVal Wb As Excel.Workbook
                       ) As List(Of Tuple(Of String, VBE.vbext_ComponentType,
                                          String, Integer, VBE.vbext_ProcKind))
        Dim VBProj = Wb.VBProject
        Dim foundProcs = New List(Of Tuple(Of String, VBE.vbext_ComponentType,
                                           String, Integer, VBE.vbext_ProcKind))
        For Each VBComp As VBE.VBComponent In VBProj.VBComponents
            Dim CodeMod As VBE.CodeModule = VBComp.CodeModule
            For LineNum As Integer = 1 To CodeMod.CountOfLines
                Dim ProcName = CodeMod.ProcOfLine(LineNum, 0)
                If ProcName IsNot Nothing Then
                    Dim ProcKind = GetProcType(CodeMod, ProcName)
                    foundProcs.Add(Tuple.Create(VBComp.Name, VBComp.Type, ProcName, LineNum, ProcKind))
                    LineNum += CodeMod.ProcCountLines(ProcName, ProcKind)
                End If
            Next
        Next
        Return foundProcs
    End Function

    Public Function GetProcType(ByVal CodeMod As VBE.CodeModule, ByVal ProcName As String) As VBE.vbext_ProcKind
        For Each ProcKind In procKinds
            If TryGetProcType(CodeMod, ProcName, ProcKind) Then Return ProcKind
        Next
        Return -1
    End Function

    Private Function TryGetProcType(ByVal CodeMod As VBE.CodeModule, ByVal ProcName As String, ByVal ProcKind As VBE.vbext_ProcKind) As Boolean
        Try
            Dim lineCount As Integer = CodeMod.ProcCountLines(ProcName, ProcKind)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
End Module

jmh

Answer 2

JMH,

An outstanding answer and solution!