Collections - Creating a Hierarchy

In fact, collections have another use in object-oriented programming in VBA. You can use collections to create an object hierarchy, just like the Data Access Object hierarchy. For example, if you cast your mind back to the MyRectangle class that we created earlier in this chapter, we could have created a Sides collection to contain a Side object for each of the four sides of the rectangle.

To do this, we would first need to create a public collection in the Declarations section of the MyRectangle class to contain the child objects.

   Public Sides As New Collection   

Next we would need to create a new Side class, with the appropriate methods and properties. For example, we could create a Side class with a Length property - corresponding to the length of the side in centimeters - and an ImperialLength property corresponding to the length of the side in inches.

Finally, we would need to add a new instance of the Side class to the Sides collection of the MyRectangle object for each of the four sides of the rectangle when the object was instantiated .

It is left to you as an exercise to implement this Sides collection, but if you want to see how it works you can see it implemented in the final version of the MyRectangle object in the Solutions database.

One thing that may not be immediately obvious is that a collection used in this way simply stores a pointer to the instance of the object in memory. We are accustomed to dimensioning a variable and referencing the object's properties via this variable:

   Dim Side as MySide     Set Side = Sides(1)     Side.Length = 1.5   

However since the collection holds a pointer to the object, you can reference object properties directly in the collection without first dimensioning a variable and using the variable.

   Sides(1).Length = 1.5   

The advantage of dimensioning the variable is that you get early binding and IntelliSense operation with the variable, whereas if you reference the object properties or methods directly in the collection, you are using late binding and you cannot see the IntelliSense help. The advantage of directly referencing the object in the collection is that you don't need to dimension the variable and perform the set statement, which will be faster for single property references.

Which method to use will be personal preference, however if you will be using a With ... End With construct to manipulate many different object properties, dimension the variable will make the code faster and more intuitive.