Collection Object

---

Syntax

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.)

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

If x = 10 then
   Set obj = New Collection
   Obj.Add Item:="Hello", Key:="Greeting"
Else
   Exit Sub
End If

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).

Example

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
    
   For i = 1 To 10
      '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

   MsgBox colMainCollection.Count
    
   For i = 1 To colMainCollection.Count
      'use the Item method to obtain a reference to the
      'subcollection
      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 and 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
      
  For i = 1 To 10
     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