1

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

T.M.
  • 9,436
  • 3
  • 33
  • 57
McFly
  • 13
  • 3
  • 1
    No. This is called relfecttion, getting information about the program. Mostly, VBA doesn't support it. – freeflow Apr 22 '21 at 17:57
  • 3
    What's the purpose of counting the variables? I suspect an [X/Y problem](https://en.wikipedia.org/wiki/XY_problem) – D Stanley Apr 22 '21 at 18:15
  • 2
    The number of fields in a UDT is never going to change at run-time. Using `2` is probably safe, but this does raise every flag of an X-Y problem. – Mathieu Guindon Apr 22 '21 at 18:21

1 Answers1

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

T.M.
  • 9,436
  • 3
  • 33
  • 57
  • 1
    Wow, @ T.M. Very nice. To me your solution makes sense. Maybe if after End Type in the module, some code is following an Exit For of the inner loop would be necessary. If my understanding is correct... @all Thank you, i think i really had an xy Problem. Tomorrow i will check my code again, if all works i will post here what i did. – McFly Apr 22 '21 at 22:02
  • Thx for response. Note: The `Exit For` is correct where it is :-) - if helpful, so feel free to accept by ticking the green checkmark. – T.M. Apr 22 '21 at 22:19
  • Ok thanks, I was thinking this way. If found And UCase(DeclLines(i)) Like "*END TYPE*" Then Exit For End If Because there could be other code after the "End Type" Statment... Ok, thank you. :-) – McFly Apr 22 '21 at 22:32