Storing Multiple Object Collections in a Collection Class

While VBA collection classes are best known for storing collections of single custom object types, they can also be built into much more complex data structures. This includes storing multiple object collections within a single collection class, permitting a node-like structure to store any number of related items.


This is the sixth post within the Tips and Tricks with VBA Classes series. Today, we discuss using a collection class to store multiple object types.

You may already be familiar with the concept of a collection class — a class module exists to encapsulate multiple instances of a single custom objects type. This forms a basic two-layer data structure. You can read more about this in our post on basic collection classes. Last week, we expanded on the basic structure by building a collection classes within other collection classes, forming a multi-level hierarchy-like data structure. This week, we explore building a collection class to encapsulate multiple instances of many custom object types. This forms a complex data structure with different nodes.

Example File

This post’s example file continues the series’ theme of collecting and printing data on countries and regions. This example file does not include any save functionality — any data which exists upon opening each instance of Excel will disappear after first use of the form. Macros must be enabled upon opening.


Collection classes are well known for storing a collection of a single custom object type. This provides easy access to multiple instances of a single object type in a 2-layer data structure. You may encounter a situation where you have two semi-related collections. Let’s say that you have two collections of custom objects: both collect data about a continent, such as countries and rivers in Europe. A schematic of the two collection classes may look something like this:

A schematic of two seperate and independent collection classes. Each will have a unique identifier in a standard module.
A schematic of two separate and independent collection classes. Each will have a unique identifier.

Based on this schematic, two separate pairs of collection classes and standard classes will exists in your project. One set will manage data on countries, while the other will manage data on rivers. Within the standard coding modules, these will also have their own unique identifier: europeCountries and europeRivers.

However, these two objects are related. They describe two components that exist within a continent. To better organize your data structure, it may make sense to include these two custom object types as two separate nodes within a single collection class. A schematic of the improved data structure appears as follows:

Two semi-related collections of custom objects are now linked together through a single collection class.
Two semi-related collections of custom objects are now linked together through a single collection class.

At first glance, this schematic may look identical to the one above, but the key difference is in the collection class. Instead of two separate collection classes, we now move to a single collection class which encapsulates two collections of custom objects. This structure is valuable for organizing complex data structures with related themes. While this schematic shows two nodes, the number of nodes possible is only limited by system memory.

Implementing this structure requires no special coding at the object level and basic semantic considerations at the collection level. The following sections provide an overview of this data structure’s use in the example file.

Custom Objects

The example file explores a data structure containing information on two related objects within a continent: countries and rivers.

Custom Object 1

The first object is the country object we’ve used in previous posts, identified as custom object type “CountryGeneral”. This object will store a country name, its population, and its land area. This is a standard class which stores three properties. No special coding or treatment is required at this (the lowest) level.

Custom Object 2

The second object (not previously used in this series) will store two details about rivers. Object type “RiverGeneral” will store a river’s name and length. Like the class above, this is a standard class with no special requirements.


The collection class is where things get quite different. You’ll recall that in the most basic implementations of a collection class, a few standard member subroutines and properties exist. These include Add (which I often rename to Insert), Count, and Item.

In the “Concept” section above, I spoke about consolidating two separate collection classes into one. Essentially, this involves moving all procedures from two separate collection classes into a single collection class. This sounds fairly easy, but it brings up an issue of naming ambiguity with member subroutines and properties — procedure names within a single module must be unique. To summarize, the two primary rules for implementing this data structure are:

  1. There must be two versions of each standard method and property (Add/Insert, Count, and Item) for each object type within the collection class.
  2. The name of each member subroutine must be unique. Including two subroutines of the same name will return an error.

To start, let’s look at the combined class module.

The following sections provide an overview of each member subroutine and property. As you may have observed above, the pairs of subroutines are nearly identical in syntax, generally only differing in semantics such as the procedure name, or the specific object/property referenced.

As mentioned above, we will need two versions of each standard member subroutine and property, each with a unique name. We’ll accomplish this by adding the words “Country” or “River” to the beginning of each member and property identifier.


In a normal (single object type) collection class, the declarations area declares a single Collection data type to store the custom objects. This is a global private variable which remains in memory until VBA is stopped (either through the editors stop button or when Excel is closed). In past examples in this series, we only declared CountryCollection in the declaration area. Now that we are storing a second custom object within the same collection class, a second Collection, RiverCollection, is declared.


If you’ve been following along in this series, you know that I refer to the commonly used Add member function as Insert. This helps distinguish between the member subroutine, and the method used within the collection data type. For a data structure which includes two collections of custom objects, we need two unique names for this procedure: CountryInsert and RiverInsert.


The item property will return a specific object within a collection based on index. The index is the incremental number automatically assigned to each new object (as it’s added) within a collection. It’s most useful for iterating through a collection within a For Loop. I’ve named this pair CountryItem and RiverItem.


If you’ve followed this series up to this point, you know that I add a separate property to access objects with a custom key. The structure of this property is similar to the Item property, but permits the passing of a String data type as an argument. I simplify this property name for easy access: Country and River.


The count property is essential for looping through collections of objects in a For Loop. I’ve named this pair CountryCount and RiverCount.

Standard Module Implementation

As with all custom objects, procedures within a standard module are used to interact with the collection class and its underlying objects.

Basic Structure

In the example file, the standard module contains a global declaration of the RegionCollection collection, identified as Europe. This collection is used to store information about both countries and rivers in Europe. Two forms exist in column B to collect information from the user. Data is them printed (as it’s added) as output in two tables: columns D through F, and column H through I.

The example file collects user inputs and outputs data in two separate lists. Multiple object collections are stored in a single collection class.
The example file collects user inputs and outputs data in two separate lists. Multiple object collections are stored in a single collection class.

To insert new data into the country and river collections, separate forms exist on the spreadsheet and separate subroutines exist in a standard module. Both subroutines access their corresponding subroutine to input new data, either CountryInsert or RiverInsert, as seen in the highlighted line in the two examples below. These are accessed like any other object method, using the dot operator between the collection class identifier (Europe) and the corresponding insert method.

Other lines within the procedure check if the object exists, uses Set it if it doesn’t, and pulls values from the Excel spreadsheet to pass as arguments in the collection insert procedures.


Two output procedures exist to print country data and river data. This is done with a For loop using each object’s count property (CountryCount and RiverCount), highlighted below. Within the loop, the CountryItem and RiverItem properties are used to reference the index of each item in the collection. While not included in this example file, should you need to call a specific object in the collection, the Country and River properties can be used to reference an object by custom key.

Working with Multiple Collections

Multiple Instances

In the example file, we create a single instance of the RegionCollection collection and identify it as Europe. You can begin to see the logic of the example used, as the Europe collection can be used to store data about countries and rivers within the European continent. Optionally, more than one instance of the collection class can be initiated, permitting organized data for more than one continent, such as including the identifier NorthAmerica or Asia.

A Collection within a Collection

If you’re ambitious, you can create a complex data structure by including this structure within another collection class. This permits a node hierarchy where parent and child collection classes further organize data — think of your drive with multiple files two or more folders deep in a file system. You can learn more about this structure in last week’s post on creating a collection class within a collection class.

In the schematic below, a parent collection class contains two collection classes, which in turn contain three objects each. If we were to apply this to the example file (hypothetically), the parent collection class would be identified as World, whereas the two child collection classes would be identified as Europe and NorthAmerica. Within those two child collection classes, Class 1 would collect country data, and class 2 would collect river data. In all, the structure would hold a total of 4 collections, 2 each of 2 different object types.

Other Notes

  • There is no real limit to the number of nodes (collections) a single collection class can contain. This is only limited by system memory.
  • While this implementation may seem complex from a high level, it only requires two collection classes to be merged and each procedure changed to a unique name.

Leave a Reply