Building a Custom Collection Class

A Collection class is a useful means to manage a large group of custom objects. The ability to store multiple classes permits the efficient operation of VBA projects, while keeping code clean and readable.

Note: If you are looking for advanced topics related to VBA classes, check out our Tips and Tricks with VBA Classes series.


This post in the Getting Started with VBA series continues the focus of classes and objects in VBA.

Classes and custom objects are one of the more complex topics in VBA. If you are VBA user with no previous experience building classes, I recommend reading our earlier posts on the topic. The following posts will provide you with a solid foundation for the material discussed here.

This post builds on the airline passenger “CRM” system used as an example in the previous posts. In previous posts, we built a custom class to collect and manage data for a single passenger record. The prior examples were limited, as only one passenger record could be managed. Today, we discuss how a collection class can manage many passenger records.

Example File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

What is a Collection Class?

A collection class can store multiple custom objects of the same data type.
A collection class can store multiple custom objects of the same data type.

It’s worth looking back at our post on normal data Collections. To summarize, a “Collection” is a data structure that can store many related elements. They can be treated as a user-friendly alternative to one-dimensional arrays. Besides basic data, collections can store other data structures. Some examples include objects, other arrays, and even other collections. In fact, the commonly referenced “Workbooks” and “Worksheets” elements are actually built-in collections. These collections contain “Workbook” and “Worksheet” objects (note plural vs. singular).

A Collection class permits the storage of many custom objects of the same object type. Your program can access properties and methods within each object of the collection. The behavior can mimic a multi-dimensional array, as objects within a collection can store many properties.

How a Class Collection Works

If you’re familiar with normal data collections, you may think building a collection for custom objects is simple. Couldn’t you just add a custom object to a collection as if it were a normal variable? While this is possible, a collection class will help you manage a large collection of objects.

Building a Class Collection

In previous posts (see links in overview section above), we created the class module Passenger to store properties and methods of custom passenger objects. This post’s example returns to that class (with some changes). However, we must also add a new class to store the collection and its associated methods and properties.

Similar to how the Worksheets collection represents Worksheet objects, we will name the class collection module Passengers to represent Passenger objects. Collection class module names do not need to be plural, but it’s worthwhile to make the name related to the objects it will be storing. Names such as”PassengerCollection” or “PassengerLedger” would also be suitable.

For instructions on how to create and name a new class modules, see this post.


The core of the collection class is a declared Collection variable to store the objects. This Collection is globally declared and private (it will only be accessible within the class module). The collection variable name in this example will be the same as its host class collection: Passengers.

Within the main coding module, the Passengers collection class is declared and instantiated as a global public variable.

The passengerGroup identifier allows access to methods within the Collection class. If required, it can be Set within an individual procedure, or it can be declared locally.


The Add subroutine permits a new class to be added to the collection.

This may look somewhat confusing, but you will begin to see the logic when broken down in parts. The Add subroutine’s purpose is to instantiate an individual object, assign values to that object’s properties, then add the object to a collection. To exemplify:

  1. Subroutine declaration that accepts parameters as values to be added to an object (line 1).
  2. Local declaration of the specific object that will be added to the collection class (line 3).
  3. Assign values (through parameter variables) to local object properties (lines 5-7).
  4. Use standard collection Add method to add object to globally declared collection (line 9).

In summary, this subroutine simply creates a new object, adds some properties, then adds the object to the collection. Calling this subroutine again—say, with a button event—will add a new object to the same collection.

We’ll need to access this subroutine in a normal coding module. The following is a variation of the Purchase_Ticket() subroutine presented in the last post.

Line 3 calls the Add method from the passengerGroup class collection. In this example, we extract values directly from the spreadsheet (the spreadsheet user inputted these values) and pass as arguments through the subroutine.


The Item subroutine permits access to the properties and methods within individual objects of the collection. This behaves just like the Item method of a collection of data.

Within this subroutine:

  1. An object index–referencing a specific object within the collection—is passed through a parameter.
  2. The identifier Item is Set to be the specific pointer in the Passengers collection, which is an object of the Passenger class.
  3. The Passenger object is passed back to the coding module as a property of the Passengers collection class.
To access an object, we need to include an individual object’s index as an argument passed through the Item method. The Item call contains a dot operator, then an object property or method. In the following example, two properties of an object within the passengerGroup collection class are called. They are called where the variable passengerNumber contains a specific object’s index.

In line 1, the object’s flightNumber is printed on Range E20. In line 2, the value in Range L20 is assigned to the passengerName property.

One important concept to understand is that when using the Item method, you are focusing on the contents of the collection class. The Collection now references a specific custom object. Intellisense will bring up all properties and methods associated with that object.


Intellisense will assist when working with collection classes.
Intellisense will aid when working with collection classes.


The Count subroutine will return the total number objects within a collection class.

The Count property is useful for iterating through collections in a For Loop. The subroutine works as follows:

  1. The Count property will extract the number of elements in the Passengers collection (line 3).
  2. The Count amount is passed as an integer through the Get Property (line 1).
In the example file’s main coding module, the Count method is used to limit a For Loop (line 8). This loop is used to build a dropdown list of indexes for the Passengers Collection (see highlighted).


The Remove subroutine will remove a specific object from the class collection. This process terminates the object and it will no longer be retrievable.

The subroutine works as follows:

  1. Collection index passes through the passengerNum parameter.
  2. The standard collection method of Remove is applied to remove a specific object from within the collection.

One important note is the behavior of the Remove method. Let’s say a collection has three objects, the index value of each object are in the order in which they are added (1, 2, and 3). If the object under index 2 is removed, the index value for the third object will change from 3 to 2. In short, objects within a collection will always be in numerical order.

A Complete Collection Class

With all components of the Collection class complete, the class module should look as follows:

A Note About Inheritance

Going back to our introductory post on custom classes, we spoke about the concept of inheritance. Specifically, that inheritance is one of the four pillars of object oriented programing which VBA does not adhere to. While it may seem like a class collection permits inheritance, it is actually not the case. Inheritance relates to how child classes can inherit properties from parent classes. If you think of a Collection class as a parent class, remember that a Collection class can only reference objects from within the Collection. An object within a Collection cannot inherit any properties from from the (parent) class collection.

That’s not to say that inheritance cannot be mimicked. In a future post, I will discuss how to use multiple Collection classes to provide inheritance-like features.

Advanced Topics

This post concludes the discussion on classes within the Getting Started with VBA series. If you are interested in learning more about classes, I suggest you look at our Tips and Tricks with VBA Classes series. This series discusses advanced topics and techniques related to classes, such as building complex class structures, creating collections of collections, and assigning arrays and objects to class properties.

Other Notes

  • Need to sort a collection class? See our post on using bubble sort with collection classes and other VBA data structures.
  • When accessing a member subroutine within a class collection, the call statement must be used if no arguments are passed.
  • A custom object’s Class_Initialize subroutine will fire when added to a class collection.
  • A custom object’s Class_Terminate subroutine will fire when removed from a class collection.


  1. A more advanced implementation would make the Item property the default member of the class (by specifying a VB_UserMemId=0 member attribute) so that myCollection(1) becomes an implicit call to myCollection.Item(1).
    Also worth noting that an object collection without an enumerator can only be iterated with a For…Next loop, which is roughly 27 times slower than iterating the same object collection with a For Each…Next loop. The enumerator member requires a VB_UserMemId=-4 member attribute, and can be implemented by accessing the hidden [_NewEnum] member of the encapsulated collection.
    FWIW I’m working on a feature that will make it easy to specify these hidden attributes, using comments: with ‘@DefaultMember and ‘@Enumerator Rubberduck annotations respectively decorating the Item and NewEnum members of a collection class, Rubberduck will verify whether the corresponding member attributes are present, and will offer to synchronize attributes and annotations, sparing you from having to export the class module, manually add the attributes in, and then re-import the modified class module.

  2. That’s interesting; thanks for the comment. So, does setting VB_UserMemId=0 permit the For Each loop to be used in custom collections? As far as I know, VBA stubbornly does not permit For Each iteration for custom collections (as opposed to intrinsic collections like Workbooks or Names).

    1. It’s Attribute MemberName.VB_UserMemId = -4 on the NewEnum member, which returns an IUnknown. IIRC with VB6 the VBE honors a flag that makes the member hidden, but it doesn’t work for user code in VBA for some reason. Anyway something like this:

      Option Explicit
      Private internal As VBA.Collection


      Public Property Get NewEnum() As IUnknown
      Attribute NewEnum.VB_UserMemId = -4

      Set NewEnum = internal.[_NewEnum]

      End Property

      With that member on the collection class, VBA understands how to enumerate the items with For Each mechanics, which is much more efficient than retrieving an item by its index: a collection class *wants* to be iterated with a For Each loop!

      And while we’re on attributes, we need to mention the VB_Description member attribute, which the VBE uses to display member descriptions in the Object Browser (F2). Rubberduck shows these descriptions in its toolbar (until we implement our own “IntelliSense”, that is!), and the corresponding member annotation is ‘@Description:

      Option Explicit
      Private internal As VBA.Collection


      '@Description("Exposes an enumerator. Enables For Each...Next enumeration. Ignore this property.")
      Public Property Get NewEnum() As IUnknown
      Attribute NewEnum.VB_UserMemId = -4
      Attribute NewEnum.VB_Description = "Exposes an enumerator. Enables For Each...Next enumeration. Ignore this property."

      Set NewEnum = internal.[_NewEnum]

      End Property

      My favorite is the module-level VB_PredeclaredId attribute, that gives your class a default instance – while this can be abused to carry global state around, but to me it has become a key to OOP in VBA: with it I can make factory methods, set VB_Exposed to True, and use that factory method in a referencing VBA project, which could not otherwise create a class instance using the class itself, instead of some global-scope factory method in some standard module.

    1. I’ve never actually seen this in practice. I just tried it with a simple custom object, and it does work. As far as I can see in my limited test, this behaves similarly to a basic collection class.

      A drawback I can see is an inability to do build complex custom object structures or create non-standard properties and methods within the collection class itself. I’ve discussed these possibilities in the “Tips and Tricks with VBA Classes” series.

      The main benefit I see to this approach seems to be the ability to run a collection of custom objects through a For Each loop, as opposed to relying on a basic For loop with counter. As a secondary benefit, this wouldn’t require an additional module for each collection class, so maybe it will save time coding for projects with many custom object collections.

      Whether it’s a good practice is hard to say. This approach does require the dictionary scripting library to be active, and it does tie you down if you want to expand functionality of the collection at some point in the future. I’d be interested in running a speed test at some point.

Leave a Reply