VBA – usage of user defined types

Probably you have heard something about PHP. If you have, then you know that it supports arrays (and lists and anything you like) and you may declare any type of data in these. In VBA, usually the case is a little different. If you have a list of integers, then you can have only integers in it, and if you have a list of doubles – only doubles.

Anyway, sometimes you may wish that VBA was a little bit like PHP, where you could have declared a list of various variables. Actually, this is possible 🙂

WIth a little work around. You should declare a Private Type, in which you may put strings, integers and anything. Let’s take a look at the example:

Imagine you have to organize a Formula1 Grand Prix. You declare the following private types:

Private Type custCar
    sCarModel       As String
    iCarSpeed       As Integer
    iCarCylinders   As Integer
End Type

Private Type custRoad
    sRoadName       As String
    sRoadLocation   As String
    dRoadDate       As Date
    aRoadCars(8)    As custCar
End Type

Private aCustRoads(8) As custRoad

Then you simply decide to assign values to these types and to let it run. In the following example I have “created” two “roads” with two cars per road. This is how it looks like:

Sub Formula1()

    Dim iCustRoad   As Integer
    Dim iCustCar    As Integer
    Dim iLoop       As Integer
    Dim iLoop2      As Integer
    Dim bPrintMe    As Boolean
    
    iCustRoad = 0
    iCustCar = 0
    
    aCustRoads(iCustRoad).sRoadName = "The road close to Sofia."
    aCustRoads(iCustRoad).sRoadLocation = "Close to Sofia."
    aCustRoads(iCustRoad).dRoadDate = Now()
    
    aCustRoads(iCustRoad).aRoadCars(iCustCar).sCarModel = "Ford"
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarSpeed = 90
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarCylinders = 8
    
    iCustCar = 1
    aCustRoads(iCustRoad).aRoadCars(iCustCar).sCarModel = "Lada"
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarSpeed = 80
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarCylinders = 4
    
    iCustRoad = 1
    iCustCar = 0
    
    aCustRoads(iCustRoad).sRoadName = "The road close to Plovdiv."
    aCustRoads(iCustRoad).sRoadLocation = "Close to Plovdiv."
    aCustRoads(iCustRoad).dRoadDate = Now()
    
    aCustRoads(iCustRoad).aRoadCars(iCustCar).sCarModel = "Ferrari"
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarSpeed = 95
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarCylinders = 10
    
    iCustCar = 1
    aCustRoads(iCustRoad).aRoadCars(iCustCar).sCarModel = "Moskvich"
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarSpeed = 81
    aCustRoads(iCustRoad).aRoadCars(iCustCar).iCarCylinders = 2
    
    bPrintMe = True
    
    For iLoop = 0 To 1 Step 1
    
        For iLoop2 = 0 To 1 Step 1
        
        If (bPrintMe) Then
            Debug.Print aCustRoads(iLoop).sRoadName
            Debug.Print aCustRoads(iLoop).sRoadLocation
            Debug.Print aCustRoads(iLoop).dRoadDate
        End If
        
        Debug.Print aCustRoads(iLoop).aRoadCars(iLoop2).sCarModel
        Debug.Print aCustRoads(iLoop).aRoadCars(iLoop2).iCarSpeed
        Debug.Print aCustRoads(iLoop).aRoadCars(iLoop2).iCarCylinders
        bPrintMe = False
        
        Next iLoop2
        
        bPrintMe = True
        Debug.Print 'this is for space
    Next iLoop

End Sub

This is the result in the immediate window:

It is actually cool, if you can use it. 🙂 Or at least, you can use it to show, that you can put various types of data in a List or array. Which is a good usage as well 😀