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

User · Accepted Answer

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     amp  MyVar MyInt  amp       amp  MyVar MyString  amp       amp  MyVar MyDoubleArr 0   amp       amp  MyVar MyDoubleArr 1   amp       amp  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     amp  MyArr i  MyString  amp       amp  MyArr i  MyInt  amp       amp  MyArr i  MyDoubleArr 0   amp       amp  MyArr i  MyDoubleArr 1   amp       amp  MyArr i  MyDoubleArr 2      Next End Sub

User · Answer

It looks like you want to define Truck as a Class with properties NumberOfAxles  AxleWeights  amp  AxleSpacings   This can be defined in a CLASS MODULE  here named clsTrucks   Option Explicit  Private tID As String Private tNumberOfAxles As Double Private tAxleSpacings As Double   Public Property Get truckID   As String     truckID   tID End Property  Public Property Let truckID value As String      tID   value End Property  Public Property Get truckNumberOfAxles   As Double     truckNumberOfAxles   tNumberOfAxles End Property  Public Property Let truckNumberOfAxles value As Double      tNumberOfAxles   value End Property  Public Property Get truckAxleSpacings   As Double     truckAxleSpacings   tAxleSpacings End Property  Public Property Let truckAxleSpacings value As Double      tAxleSpacings   value End Property   then in a MODULE the following defines a new truck and it s properties and adds it to a collection of trucks and then retrieves the collection   Option Explicit  Public TruckCollection As New Collection  Sub DefineNewTruck   Dim tempTruck As clsTrucks Dim i As Long       Add 5 trucks     For i   1 To 5         Set tempTruck   New clsTrucks          Random data         tempTruck truckID    Truck   amp  i         tempTruck truckAxleSpacings   13 5   i         tempTruck truckNumberOfAxles   20 5   i           tempTruck truckID is the collection key         TruckCollection Add tempTruck  tempTruck truckID     Next i       retrieve 5 trucks     For i   1 To 5          retrieve by collection index         Debug Print TruckCollection i  truckAxleSpacings          retrieve by key         Debug Print TruckCollection  Truck   amp  i  truckAxleSpacings      Next i  End Sub   There are several ways of doing this so it really depends on how you intend to use the data as to whether an a class collection is the best setup or arrays dictionaries etc

[class] Use of Custom Data Types in VBA

Examples related to class

Examples related to vba

Examples related to types

Examples related to excel