Counting Type Variables in Excel vba, possible?

Question

Currently im working with excel vba. I'm asking myself if there is a possibility to count the variables declared in a user defined Type as shown below.

Public Type NameOfType
   a as string
   b as string
End Type

Here the result would be 2.

Thank you

Answer 1

Approach via VBA Extensibility library

Programmatic access to the VBA Project assumed, you can

• extract its code modules' declaration heads and
• analyze the relevant codelines

(reference to 'Microsoft Visual Basic for Applications Extensibility 5.3' needed).

Sub CountTypeVars(ByVal StatementName As String)
    Dim VBAEditor      As VBIDE.VBE              ' VBE
    Dim curProject     As VBIDE.VBProject        ' Projekt
    Dim curComponent   As VBIDE.VBComponent      ' Modul
    Dim curCode        As VBIDE.CodeModule       ' Codeblock des Moduls
    Dim i              As Integer
    ' ========================================
    ' Get the project details in the workbook.
    ' ========================================
    Set VBAEditor = Application.VBE
    Set curProject = VBAEditor.ActiveVBProject
    
    For Each curComponent In curProject.VBComponents    ' check all MODULES
        ' Find the code module for the project (Codeblock in current component/=module).
        Set curCode = curComponent.CodeModule
      
        Dim ii As Long
        ii = curCode.CountOfDeclarationLines
        Dim DeclLines: DeclLines = Split(curCode.Lines(1, ii), vbNewLine)
        Dim cnt As Long, found As Boolean
        cnt = 0
        For i = LBound(DeclLines) To UBound(DeclLines)
            If UCase(DeclLines(i)) Like "* " & UCase(StatementName) & "*" Then
                Debug.Print "** Type Statement : ", DeclLines(i)
                Debug.Print "   Found in Module: ", curComponent.name & vbNewLine & String(50, "-")
                Debug.Print "Line #", "Count #", "Variable(s)" & vbNewLine & String(50, "-")
                found = True: i = i + 1
            End If
            If found And Not UCase(DeclLines(i)) Like "*END TYPE*" Then
                cnt = cnt + 1               ' increment variable counter
                Debug.Print "# " & i + 1, cnt, VBA.Trim(DeclLines(i))
            End If
                
        Next i
        If found Then
            Debug.Print vbNewLine & "** Counted " & cnt & " Type Variables."
            Exit For
        End If
    
    Next
    If Not found Then Debug.Print "** No occurrence of " & StatementName & " found!"
End Sub

Example Output in VB Editor's immediate window

Calling e.g. CountTypeVars "NameOfType" you might get the following results:

** Type Statement :         Public Type NameOfType
   Found in Module:         modExample
--------------------------------------------------
Line #        Count #       Variable(s)
--------------------------------------------------
# 4            1            a As String
# 5            2            b As String

** Counted 2 Type Variables.

Caveat

This approach counts each codeline following the Type statement as one variable; so further code lines such as line breaks or comments aren't handled.