5.4. Collection Objects

Many of the classes that make up an object model are in fact collections of other classes or objects. You will find that these classes have several generic methods and properties, such as Add, Item, and Count. The Add method creates a new member of the collection stored against a unique key, the Item method returns a member of the collection given a unique key or index value, and the Count method returns the number of members in the collection.

You may find, however, that the collection object's standard Item method has been wrapped within a property, the name of which is the same as the name of the class held by the collection. A call to this property returns an object of the type of the class held by the collection. For example, an Employees collection object may implement an Employee property that returns an Employee object, like this:

Public Property Get Employee(vEmpCode As Variant) As Employee
    Set Employee = McolEmployees.Item(vEmpCode)
End Property

Collections within object models are most commonly at the top of an object hierarchy or at the top of a branch of an object hierarchy, and are most likely createable with the New keyword or the CreateObject function. Access to individual objects is gained through the collection. For example, the Worksheets object spawns a Worksheet object; a Cells object spawns a Cell object.

5.4.1. Referencing by Name or Number

A correctly implemented collection object allows you to access the members of the collection either by key name or by ordinal position in the collection. This is handled by the Item method, which accepts a variant as its single parameter. If the parameter is a string value, Item tries to match the parameter with a key in the collection. On the other hand, if the parameter is numeric (including the string representation of a number), the Item method uses this number to return the item in that ordinal position. This is demonstrated by the following snippets:

sEmployeeName = adoRecordset.Fields.Item(1)

or:

sEmployeeName = adoRecordset.Fields(1)

or:

sEmployeeName = adoRecordset.Fields("Empname")

You will also note from the above snippets that because Item is the default method of the collection object, it can be called implicitly.

5.4.1.1. For Each...Next

You can iterate through a collection by obtaining its Count property value, which returns the number of items in the collection, and executing a For...Next loop, each time setting an object variable to the new collection member, as follows:

For i = 1 To oColl.Count
    Set oCollMember = oColl.Item(i)
        'do some stuff with oCollMember
    Set oCollMember = Nothing
Next i

However, VB provides a more efficient method: the For Each...Next loop. The For Each...Next loop iterates through the collection, automatically assigning a reference to the current member of the collection, then exiting once the end of the collection has been reached, as this rewritten snippet shows:

For Each oCollMember in oColl
    'do some stuff with oCollMember
Next

Using For Each...Next is also safer because collection members can be automatically reindexed by the actions of another part of the program—for example, if a member is removed from the collection—which means that stored index numbers shouldn't relied upon.

For more information about the Collection object and the For Each...Next statement, see their entries in Chapter 7.