excelvbatypesvbe

Counting Type Variables in Excel vba, possible?

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

Solution

  • Approach via VBA Extensibility library

    Programmatic access to the VBA Project assumed, you can

    (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.