Collection Object


Dim objectvariable As [New] Collection Set objectvariable = [New] Collection objectvariable Use: Required

Data Type: Collection The name of the Collection object. Description

A Collection object allows you to store members of any data type, object, control, class, or another collection, and to retrieve them using a unique key. You can therefore create a structured collection object containing referential data. The real power of a collection comes by using collections with user-defined classes. (You can find details of creating and using class modules in Chapter 4, Class Modules.)

The collection is an intrinsic VBA object. VBA offers two method of creating a collection. The first uses the New keyword in the collection declaration; for example:

Dim obj As New Collection

Obj.Add Item:="Hello" Key:="Greeting"

Using the New keyword within the Dim statement forces an implied Set statement, which causes the Collection object to be instantiated at that point. The second syntax is:

Dim obj As Collection

Set obj = New Collection

Obj.Add Item:="Hello" Key:="Greeting"

In this second method, a Set statement is required to instantiate the collection, and is preferable in situations where the creation of the object is the result of a conditional statement, because if the condition fails, the collection isn't instantiated, and memory is saved. (Memory is still reserved for the collection, but there isn't the overhead involved in creating the collection.) In contrast, using the first syntax, the collection resides in memory, perhaps needlessly, regardless of the result of the conditional statement. The following code fragment, for instance, illustrates the use of the Dim and Set statements to conditionally create a Collection object:

Dim obj As Collection

Set obj = New Collection Obj.Add Item:="Hello", Key:="Greeting" Else

Exit Sub End If

164 Chapter 7- The Language Reference

The New keyword is required to create an instance of a collection that has not been instantiated.

You can add only one piece of data, object, or another collection to a particular "position" within a collection. This may at first glance seem somewhat limited. However, the important point to note is that you can add a collection to a collection; in fact, there is no limit to nesting collections within collections.

Rules at a Glance

• You can use a Collection object to store any data type, control, object, or another collection.

• Only one piece of data, etc. (known as a member) can be stored in each collection location (see the Collection.Add method).

• Members of a collection can be accessed either by using their ordinal number or by referring to the member's Key, assuming that one was assigned at the time that the member was added to the collection (see the Collection.Item method).

• Use the Count method to return the number of members in the collection.

• The first member in a collection is stored at ordinal position 1 (not at 0, as is the default for an array).


This example shows how you can nest one collection within another. Basically, 10 instances of colSubCollection are created, each containing two integer values. These 10 colSubCollection objects are stored within colMainCollection. The code also shows how to read back the values of colMainCollection and colSubCollection:

Sub testCollection()

'declare objects for the main and sub collections 'creating a new instance of the main collection 'in the process

Dim colMainCollection As New Collection Dim colSubCollection As Collection

'create a new instance of the sub collection object Set colSubCollection = New Collection 'populate the sub collection with two integer values colSubCollection.Add Item:=i + 6, _

Key:="MySixPlusVal" colSubCollection.Add Item:=i + 3, _

Key:="MyThreePlusVal" 'now add the sub collection to the main collection 'using the count converted to a string as the key colMainCollection.Add Item:=colSubCollection, _

Key:=CStr(i) 'destroy the reference the sub collection Set colSubCollection = Nothing Next i

Collection Object 165

MsgBox colMainCollection.Count

For i = 1 To colMainCollection.Count

'use the Item method to obtain a reference to the


Set colSubCollection = _

colMainCollection.Item(CStr(i)) 'display the values held in the sub collection. Debug.Print "6 + " & i & " = " & _

colSubCollection.Item("MySixPlusVal") Debug.Print "3 + " & i & " = " & _

colSubCollection.Item("MyThreePlusVal") 'destroy the reference to the sub collection Set colSubCollection = Nothing Next i

End Sub Programming Tips & Gotchas

• A highly efficient method of enumerating the members of a collection uses the For Each...Next loop, as the following example shows:

Dim colMyCollection As New Collection Dim colSubCollection As Collection

Set colSubCollection = New Collection colSubCollection.Add Item:=i + 6, _

Key:="MySixPlusVal" colSubCollection.Add Item:=i + 3, _

Key:="MyThreePlusVal" colMyCollection.Add Item:=colSubCollection, _

Key:=CStr(i) Set colSubCollection = Nothing Next i

For Each colSubCollection In colMyCollection

MsgBox colSubCollection.Item("MySixPlusVal") Next

• The recommended three-character code convention for the collection object is "col".

• If you are using VB6, you should also take a look at the Dictionary object, which is similar to the Collection object but operates faster and provides more built-in functionality.

See Also

Collection.Add Method, Collection.Count Property, Collection.Item Method, Collection.Remove Method, Dictionary Object, Dim Statement, For Each...Next Statement, Set Statement

166 Chapter 7- The Language Reference

Was this article helpful?

0 0

Post a comment