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

								

		

2 Responses to “VBA – User Defined Type”

1. federal grants says:

   August 25, 2010 at 12:03 am

   Great site. A lot of useful information here. I’m sending it to some friends!

   Reply
   • zbert says:

     August 29, 2010 at 6:28 pm

     Thanks, sorry I’ve not added much this past month… I plan to put up some more posts soon.

     Reply