Creating a Collection Class within a Collection Class

A common object structure in VBA is the collection class, where many custom objects are grouped together. VBA also permits additional layers above the collection class, resulting in a collection within a collection. This is a useful technique for organizing hierarchical data or grouping certain objects.

Overview

This is the fifth post in the Tips and Tricks with VBA Classes Series. Today, we discuss building a collection within a collection.

The object model in VBA, while not true object oriented programming, is quite versatile for storing data off a spreadsheet. The post Building a Custom Collection Class, part of the Getting Started with VBA¬†series, introduces us to building a collection of custom objects — a two-layer object structure where a collection holds many individual custom objects. For some complex projects, there may be a need to further organize a collection of objects. One organization method includes adding a third layer to the collection/object hierarchy, where a collection itself is stored within another collection. This gives the collection a node-line structure, where specific objects can be organized into multiple groups.

This post refers to the concept as building a “collection within a collection”. You may have also heard this concept refered to as a “collection of collections” or a “nested collection”.

Example File

This post continues the theme of country details, however, I’ve taken out all features developed in prior posts to provide a clean example. While I always try to build on past concepts, this topic is a bit advanced. Removing all the other features to focus solely on the main concept will help make the tutorial more clear.

Concept

If you’ve followed this series up to this point, you know that previous posts have worked with custom objects containing details about countries (countryDetails) which are contained within a collection (countryCollection). Today’s post adds another layer to that data structure — a new collection (regionCollection) now groups collections of countries into regions.

The basic schema is as follows: regionCollection (collection) > countryCollection (collection) > countryDetails (custom object). The regionCollection class can store multiple instances of the countryCollection class, which in turn can store many instances of the countryDetails class. This is a node-like hierarchical structure.

You may not realize it, but you have very likely worked with collections within collections many times in VBA. Does the following look familiar?

That schema is: Workbooks (collection) > Worksheets (collection) > Range (object) > Value (property)

The goal of this line of code is to update the Value property of the Range object at address “A1” within worksheet “Sheet1” which itself is within the “ActiveWorkbook”. While it seems straightforward, there are a few steps to get there. First, we must specify the parent object, which in this case is “ActiveWorkbook”. Next, we must access the Worksheet object, “Sheet1” within the Worksheets collection. We do this by referencing the specific Worksheet by name (key). Finally, we can access the Range object and pass the argument “A1” to specify the specific cell. From this point, we can access all methods and properties of the Range object.

Keep this idea in mind as we begin to work with the CountryDetails object. Here’s a snippet that can be used to update a country’s population:

With this snippet, our primary objective is accessing the countryPop property of the CountryGeneral object. To get there, we include references to the regionCollection collection and the countryCollection collection. You’ll see a similar syntax when looking directly above at the Range object reference — semantics change, but syntax is identical. You’ll notice that even though we are accessing a property of the CountryGeneral object, we do not explicitly use the CountryGeneral identifier. This is because the reference passed through the Region and Country properties simply point to the CountryGeneral object.

Implementation

Planning is essential when working with complex object structures. A quick schematic will help you determine your best approach to coding.

If a collection class will group a number of custom objects, a collection within a collection simply holds a collection inside of another collection. The general data structure can be visualized in the following schematic.

Schema of a basic collection of a collection class structure.
Schema of a basic collection of a collection class structure.

Within this schematic, columns represent individual classes (class modules) while rows represent specific components of each class. The arrow lines represent links between the object and classes, each being linked by a unique index and key.

In the following sections, we explore each part of the schematic in detail. For the purpose of clarity, the explanations are ordered from right (custom objects) to left (parent collection).

Custom Objects

Custom objects are the lowest level and most specific part of a collection within a collection object structure. In the example file, custom objects are used to store details about specific countries. This includes country name (countryName), population (countryPop), and land area (countryLandArea). At this point in the construction of the object structure, there’s no need to deviate from the traditional standards of building a simple class. The schema above includes “Element Index” and “Element Key” above the object as the child collection will assign each object an index and key — this does not impact how we create the class model.

Child Collection

If you’ve been following along with the series up to this point, previous posts have worked with a standard collection class which contains a number of countries. We’re going to continue using this class as the child collection in this example. Keep in mind that we’ve stripped away a number of member subroutines and functions we used in previous posts (this helps make this posts’ explanation clearer).

At first glance, the code below may look like a fairly standard collection class. However, there is one difference — this collection class now contains a property: mRegionName. Given that our data structure will now group collections of countries in a region, we want to store a region name on each collection of countries. This class module includes both a Private declaration of the String mRegionName, as well as associated Let and Get properties.

We also continue to include a property to return a custom object using a key. This can be seen in the Country property. This works similarly to the traditional Item property, but permits the use of a String value to access a custom key, as opposed to an integer value to access an index. If you’d like to find out more about the rationale for allowing access to custom objects with both custom keys and their index, see this post on¬†using custom keys in a collection class.

Given that this collection is also held within another collection, there is an associated index and key with each instance of the collection. This, however, does not require any additional coding in the current module.

Parent Collection

The parent collection, while also looking like a typical collection class, is the centerpiece of the collection within a collection object structure.

Within the Insert subroutine, instead of instantiating a new custom object for storage within the collection, a new collection (CountryCollection) is instantiated. Additionally, the property regionName is assigned to the name of the new collection (child) class.

Working with a Collection Within a Collection

Adding Objects

If you take a look at the parent and child collection coding examples above, you will notice each has an Insert subroutine. This subroutine acts as the class method which adds new elements to the collection. Within a standard coding module, we can use the Insert method like we would with any other custom object, but it’s important to make sure we’re referencing the correct collections to keep our three-layer object structure together. After an initial setup (described below), line 23 shows how we can access the Insert method of the CountryCollection collection. This will add a new CountryDetails custom object.

While the bulk of the functionality is in line 20, it’s important to do a few checks before inserting a new country:

  1. Confirm that the RegionDetails parent collection exists. If it does not, set the collection (line 10).
  2. Confirm that the region being referenced to include the new country exists (lines 19-21). If not, use the Insert method to create a new region.
  3. Confirm that the country does not already exist within the CountryDetails collection (lines 22-24).
  4. Create the new country using the Insert method (line 23).

Printing

Given that all elements of both collections are assigned both a key and an index, there are two methods of accessing custom objects stored within collections of collections.

Loop Through Collections

The easiest way to print all content within the collections is with a nested For Loop. Using the index of the two collections, we can iterate through each custom object.

Output looks like a folder hierarchy when printing the collection within a collection.
Output looks like a folder hierarchy when printing the collection within a collection.

A nested loop is used to iterate through both levels of the collection. This may seem familiar — it almost looks like printing a 2D array. In the example file, region names are printed in column D, while country names along with their population/land area data are printed in columns E through G. The end result is a folder-like (node) hierarchy.

Call Specific Objects

If there’s no need to output all object data, collection keys can be used to output information on a specific object, mimicking search functionality. In practice, if we know the key beforehand, we know that there are specific details we want to extract from that data.

This may look slightly complex, but focus your attention on the two highlighted lines (12 and 13). These are the only two lines which access the object. The bulk of the remaining script work to ensure that the region and country named entered into the worksheet actually exist. An alternative to this approach would be to provide the user with a dropdown list containing all available countries and regions.

Other Notes

  • With this object structure, custom objects can have identical keys as long as they exists within a different node of the collection hierarchy. Identical keys within the same node will result in an error.
  • The example file also includes two user-defined functions which determine if regions or countries already exist in the object structure.
  • Even if class modules have been built with a three-layer “collection within a collection” framework, it’s still possible to instantiate objects independent of collections.
  • While this post recommends including a collection’s key name as a property within its underlying object, this is not required. Doing so will give read access to the key value of an object/collection stored within a collection. Keep in mind that it is not possible to read a key value from a collection.
  • While this post will introduce us to the three-layer object structure of a collection within a collection, it’s also possible to add more than three data layers. There is theoretically no limit, only constrained by over-complexity and system processor limitations.
  • I avoid calling this concept a “collection of collections” as that implies that a collection can hold many collections. This is actually possible, but it is out of the scope of this post. A future post will discuss a collection containing multiple custom object types, which revolves around a similar framework of a collection of collections.

4 Comments

    1. Do you mean referencing a child collection, or passing a collection through a function ByRef? In both cases, you can point to a child collection using the “Item” property and assigning it to an identifier of the child collection data type:

      Dim childCollectionRef As ChildCollectionDataType
      childCollectionRef = ParentCollection.Item(i)

      To pass through a function by Ref:

      result = customFunction(ByRef childCollectionRef As ChildCollectionDataType)

      OR

      result = customFunction(ByRef ParentCollection.Item(i) As ChildCollectionDataType)

      The variable “i” would be the collection element being referenced. I hope this helps. Let me know!

Leave a Reply