VBA – User Defined Type

As much as I do not like VBA, I do like the “User Defined Types” which, from a C/C++ programmer’s prospective, this is a lot like using structs.  And for data management, which is probably 90% of what VBA is used for, it makes a lot of since.  How do we define a User Defined Type?

Private Type dict
    a As Integer
    b As String
    c As String
    d As Integer
End Type

Once Defined, we need an initialization function to store the struct.

Private Function defineDict() As dict
    defineDict.a = 1
    defineDict.b = "two"
    defineDict.c = "three"
    defineDict.d = 4
End Function

At this point, we can use the same calling to to that ‘struct’ to change the variables as needed.

Sub testType()
    Dim t As dict       ' Set the variable
    t = defineDict      ' Initialize
    Debug.Print t.b     ' Prints >> "two"
    t.b = "no more"     ' Change a variable
    Debug.Print t.b     ' Prints >> "no more"
End Sub

4 thoughts on “VBA – User Defined Type”

Leave a Reply

Your email address will not be published. Required fields are marked *