Use of Custom Data Types in VBA

Question

I am trying to create a custom data type in VBA for Excel. Let's call this data type "truck". Each truck has the following attributes:

NumberOfAxles (this is an integer) AxleWeights (this is an array of doubles) AxleSpacings (this is an array of doubles) 

Can I create many instances of the data type "truck" (truck(1), truck(2)... etc), and read/write the attributes I listed above to that instance?

Example:

Truck(1).NumberOfAxles = 2 Truck(1).AxleWeights(1) = 15.0 Truck(1).AxleWeights(2) = 30.0 Truck(1).AxleSpacings(1) = 8.0  Truck(2).NumberOfAxles = 3 Truck(2).AxleWeights(1) = 8.0 Truck(2).AxleWeights(2) = 10.0 Truck(2).AxleWeights(3) = 12.0 Truck(2).AxleSpacings(1) = 20.0 Truck(2).AxleSpacings(2) = 4.0 

and so on. The syntax above is most possibly wrong, I just wanted to demonstrate the structure I need to come up with.

All I am trying to write data to a data structure and call it as necessary such as

Truck(i).NumberOfAxles Truck(i).AxleWeights(j) Truck(i).AxleSpacings(j) 

Thank you very much!

Answer 1

Sure you can:

Option Explicit  '***** User defined type Public Type MyType      MyInt As Integer      MyString As String      MyDoubleArr(2) As Double End Type  '***** Testing MyType as single variable Public Sub MyFirstSub()     Dim MyVar As MyType      MyVar.MyInt = 2     MyVar.MyString = "cool"     MyVar.MyDoubleArr(0) = 1     MyVar.MyDoubleArr(1) = 2     MyVar.MyDoubleArr(2) = 3      Debug.Print "MyVar: " & MyVar.MyInt & " " & MyVar.MyString & " " & MyVar.MyDoubleArr(0) & " " & MyVar.MyDoubleArr(1) & " " & MyVar.MyDoubleArr(2) End Sub  '***** Testing MyType as an array Public Sub MySecondSub()     Dim MyArr(2) As MyType     Dim i As Integer      MyArr(0).MyInt = 31     MyArr(0).MyString = "VBA"     MyArr(0).MyDoubleArr(0) = 1     MyArr(0).MyDoubleArr(1) = 2     MyArr(0).MyDoubleArr(2) = 3     MyArr(1).MyInt = 32     MyArr(1).MyString = "is"     MyArr(1).MyDoubleArr(0) = 11     MyArr(1).MyDoubleArr(1) = 22     MyArr(1).MyDoubleArr(2) = 33     MyArr(2).MyInt = 33     MyArr(2).MyString = "cool"     MyArr(2).MyDoubleArr(0) = 111     MyArr(2).MyDoubleArr(1) = 222     MyArr(2).MyDoubleArr(2) = 333      For i = LBound(MyArr) To UBound(MyArr)         Debug.Print "MyArr: " & MyArr(i).MyString & " " & MyArr(i).MyInt & " " & MyArr(i).MyDoubleArr(0) & " " & MyArr(i).MyDoubleArr(1) & " " & MyArr(i).MyDoubleArr(2)     Next End Sub