Use of Custom Data Types in VBA

Use of Custom Data Types in VBA

Asked on January 11, 2019 in Excel.
Add Comment


  • 2 Answer(s)

    The best solution is here :

    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
    
    Answered on January 11, 2019.
    Add Comment

        It glance a requirement to assign Truck like a Class with properties NumberOfAxles, AxleWeights & AxleSpacings.

    This can be assigned in a CLASS MODULE and a named  as 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
    

        After that, In a group of trucks and then retrieves the groups,  in a MODULE  the below assigns a new truck and it’s properties and adds it.

    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" & 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" & i).truckAxleSpacings
     
         Next i
     
    End Sub
    

        So many ways of doing this so it really depends on the intend to use the data like to either an a class/collection is the most excellent setup or arrays/dictionaries.

    Answered on January 11, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.