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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
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 😀