Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Creating and Working with Custom Collections

Earlier in this chapter, I discussed the problems associated with arrays. If you are unsure of the number of elements that the array will contain, fixed arrays can take up large amounts of memory unnecessarily. On the other hand, the resizing of dynamic arrays is rather inefficient. Finally, all the elements of an array must be contiguous, and the arbitrary identifier for the array element is meaningless. The answercustom collections. Custom collections can contain values and objects. You can easily add items to, and remove items from, a collection. VBA identifies each element in the collection by a meaningful unique key.

In summary, custom collections are similar to arrays, but they offer several advantages:

• Collections are dynamically allocated. They take up memory based only on what's in them at a given time. This is different from arrays, whose size you must either predefine or re-dimension at runtime. When you re-dimension an array, Access actually makes a copy of the array in memory, taking up substantial resources. By using custom collections, you can avoid this consumption of extra resources.

• A collection always knows how many elements it has, and you can easily add and remove elements.

• Each element of a collection can contain a different type of data.

• You can add elements into any element of a collection.

NOTE

You can find the code examples in this section in the basCollections module of the CHAP12EX.MDB database.

Creating a Custom Collection

You create a collection using a Collection object. After you declare the Collection object, you can add items to the collection. The code necessary to create a custom collection looks like this:

Dim colNames as Collection

Adding Items to a Custom Collection

You use the Add method of the Collection object to add items to a custom collection. The Add method receives a value or object reference as its first parameter, and a unique key to that element of the collection as its second parameter. The Add method appears as follows:

colNames.Add "Alexis", "Alexis"

The code shown previously adds the name Alexis to a collection called colNames. The key to the item in the collection is the name Alexis. In the following code example, the collection colNames is first declared and instantiated. The code then adds several names to the custom collection colNames.

Sub AddToCollection()
'Declare a Collection object
Dim colNames As Collection
'Instantiate the Collection object
Set colNames = New Collection
'Add items to the collection
colNames.Add "Alison", "Alison"
colNames.Add "Dan", "Dan"
colNames.Add "Alexis", "Alexis"
colNames.Add "Brendan", "Brendan"
colNames.Add "Sonia", "Sonia"
colNames.Add "Sue", "Sue"
End Sub

CAUTION

Unlike almost every other array or collection in VBA, custom collections are one-based rather than zero-based. This means that the element numbers begin with one rather than zero. This is a big change if you're used to thinking of arrays and collections as always zero-based.

Accessing an Item in a Custom Collection

After you have added items to a collection, you use the Item method to access them via either their ordinal position, or the key designated when you added them. Accessing an item in a collection using the ordinal position looks like this:

Debug.Print colNames.Item(1)

Because the Item method is the default method of the Collection object, you can shorten the code to this:

Debug.Print colNames(1)

I usually prefer to refer to an item in a collection using its unique key. The code appears as follows:

Debug.Print colNames("Alexis")

Removing Items from a Custom Collection

You use the Remove method of the Collection object to remove items from a collection. The syntax looks like this:

colNames.Remove 2

The preceding syntax would remove the second element of the collection. Using the key, the code is changed to this:

colNames.Remove "Sonia"

You can easily remove all the elements of a collection in two ways:

Set colNames = New Collection

or

Set colNames = Nothing

Iterating Through the Elements of a Custom Collection

You use the For...Each loop to iterate through the items in a collection. The code looks like this:

Sub IterateCollection()
'Declare a Collection object
Dim colNames As Collection
'Declare a variant variable for looping
'through the collection
Dim varItem As Variant
'Instantiate the Collection object
Set colNames = New Collection
colNames.Add "Alison", "Alison"
colNames.Add "Dan", "Dan"
colNames.Add "Alexis", "Alexis"
colNames.Add "Brendan", "Brendan"
colNames.Add "Sonia", "Sonia"
colNames.Add "Sue", "Sue"
'Use the variant variable and a For..Each
'loop to loop through each element in
'the collection, printing its value
For Each varItem In colNames
Debug.Print colNames(varItem)
Next varItem
End Sub

Notice that in addition to the declaration of the Collection variable, the code declares a Variant variable. The code uses the Variant variable in the For...Each loop to loop through each item in the collection. The Variant variable is the subscript within the For...Each loop for accessing a particular item within the collection.