VB & VBA in a Nutshell: The Language

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