A collection is an object that represents a set of variants. Each variant in the collection is traditionally of the same data type or class but this in not necessarily true.

A collection is similar to an array in concept (both are sets of scalar values or objects) but is basically different:

Collections can refer to their members by a Key (aka identifier. A string.), or by an Index (aka position. A long integer that can be from 1 to the Count property value.). The following examples all refer to a member of the collection:


A recordset is conceptually a collection and also has this additional syntax for accessing its member (i.e. fields):


A collection object has one property and just a few methods (Note that some objects may have collections with other properties and methods.).

For an example of how you might implement a collection in your own class, see Property Procedures.

Steps to Implement a Collection Class

The following list summarizes the steps required to create a collection class.

  1. Add a class module to your project, and give it a name -- usually the plural of the name of the object the collection class will contain. EG: Employees.
  2. Add a private variable to contain a reference to the Collection object your properties and methods will delegate to. EG: Private mEmployees As New Employees.
  3. In the Class_Initialize event procedure, create the Collection object. (If you want to defer creation of this object until it's needed, you can declare the private variable in step 2 As New Collection. This adds a small amount of overhead each time the Collection is accessed.)
  4. Add a Count property and Add, Item, and Remove methods to your class module; in each case, delegate to the private Collection by calling its corresponding member. EG:
    Public Sub Delete(ByVal Index As Variant)
       mcolEmployees.Remove Index
    End Sub
  5. When you implement the Add() method, you can override the behavior of the Collection object's undiscriminating Add method by accepting only objects of one type. You can even make it impossible to add externally created objects to your collection, so that your Add method completely controls the creation and initialization of objects.
  6. Use the Procedure Attributes dialog box to make the Item method the default for your collection class.
  7. Add a NewEnum method, as shown below. Use the Procedure Attributes dialog box to mark it as hidden, and to give it a Procedure ID of -4 so that it will work with For Each Next. The following assumes that the private variable in step 2 is named mcol.
    Public Function NewEnum() As IUnknown
       Set NewEnum = mcol.[_NewEnum]
    End Function
  8. Add custom properties, methods, and events to the collection class.


Page Modified: (Hand noted: ) (Auto noted: )