The Collection Object

VBA features one generic object type, the Collection object, which is simply a container for data. Although typically its members are other objects, it can in fact hold data of any type, including other collection objects. The collection object is therefore an object-oriented version of the Visual Basic array. It supports the following four methods:

Adds an item to the collection. Along with the data itself, you can specify a key value by which the member can be retrieved from the collection.


Returns the number of items in the collection.


Retrieves a member from the collection either by its index (or ordinal position in the collection) or by its key (assuming that one was provided when the item was added to the collection).


Deletes a member from the collection either by its index or its key.

For example, the following code fragment defines a collection object, colStates, to hold U.S. state information, and adds two members to it that can later be accessed by their key, which in this case happens to be their two-letter state code:

Dim colStates As New Collection colStates.Add "New York", "NY" colStates.Add "Michigan", "MI"

As we've noted, collection objects, like arrays, are containers for data. Like the elements in arrays, the members of collections can be iterated using the For Each...Next construct. And like arrays, they are accessible by their index value, although the lower bound of a collection object's index is always 1, and can't be set otherwise in code. But given the similarity to arrays, why use collection objects, rather than arrays, in your code? The major reason is ease of access and ease of maintenance:

• Members can be added before or after an existing member based on the lat-ter's key value as well as its index value.

• Members can be retrieved based on either their key value or their index value.

• Members can be deleted based on either their key value or their index value. Multiple deletions based on an index value, however, should be done back-

Object Variables and Binding 49

wards, from higher index values to lower ones, since the collection is rein-dexed after each deletion.

Was this article helpful?

0 0

Post a comment