Using Methods and Properties within a Collection Class

The VBA collection class provides a framework to support basic object structures. While its most common use a collection class to manage groups of custom objects, including new properties and methods permits extraction of key aggregate data points for the entire collection.

Overview

This is the eight post in the Tips and Tricks with VBA Classes series. Today’s post provides an overview of using properties and methods within a collection class. This is not to be confused with last week’s post, Forcing Class Properties to do More Work, which focused on building validation scripts and calculations within properties of standard (non-collection) classes.

Example File

This post’s example file continues the theme of a region/countries for a collection class and its child objects. This file was built strictly to showcase the topics discussed in this post. That being said, procedures do not include any class save or validation functions — you may encounter errors for entering invalid data formats or duplicate keys. Macros must be enabled upon opening.

Concept

If you’ve followed the series up to this point, you’re aware that properties exist in standard (non-collection) classes to manage data stored within custom objects. The Let property adds or updates information in a class, while the Get property outputs information from a class.

We encounter unique opportunities when working with collection classes. By their nature, collection classes typically do not store specific data points. Instead, they store multiple instances of custom objects built from an underlying class. These instances of custom objects can hold numerous data points in themselves, but the data is in no way inherited from, or dependent on the collection class. However, this does not mean the collection class cannot provide information about it’s contents.

You’ve likely already used a collection class property for this purpose. Most beginner guides on collection classes suggest creating a Count property. The Count property extracts information about the collection, specifically how many objects are stored within it. While this property is useful for a mechanical use—such as acting as the upper-bound in a For Loop—the approach is also useful for extracting aggregate information from a collection class. One example could be an aggregate sum of totals stored within a collection’s child objects.

Collection Class Properties

Building on the example file’s collection of countries, we can begin to think about the data we want to extract within the collection class.

  • If we build a collection class of countries, we can use the Get property within the collection class to calculate the entire population of the collection (e.g. a region).
  • Also using the Get property, we can output the most populous and least populous countries.
  • We can use the Let property to force the collection to update all details of underlying objects with a single call.

It’s important to emphasize that no actual data is stored or manipulated within the collection class itself. Use of the Get property accesses elements of the collection. Use of the Let property updates individual elements of the collection.

Extracting Aggregate Data

Extracting aggregate data from a collection class within a Get property is an effective way to present information about the collection as a whole. In the example file, a For Loop cycles through each country in the collection while summing its total population. This gives us a total population for all countries in the collection/region.

Basic schematic showing the class level property regionPopulation to sum properties within its child collection.The schematic to the left presents a basic example of how the above procedure operates. The RegionCollection collection contains two countries. The regionPopulation property within the collection class cycles through each country in the collection (CountryGeneral), extracting the countryPop value from each child class.

Extracting Maximum and Minimum

Using a For Loop, we can extract the maximum and minimum population values through a simple conditional and variable assignment.

Return the largest population

Return the smallest population

Inputting Bulk Calculations

It’s also possible to work with the Let property in collection classes. This is useful for performing “bulk” calculations on all elements in a collection. In the example file, a Let property permits a population to be increased by a percentage value entered into cell B13. The procedure itself is operated by the “Increase” button. In practice, this could be useful for a scenario analysis to see the impact an increase in population may have on various models.

Note: A country must exist in memory for this procedure to work. You’ll notice in the example file that clicking the “Increase” button when no class is in memory will return a “not set” error.

Collection Class Methods

Traditional methods (member functions and member subroutines) can also be used within a collection class. Member functions can behave very similarly to the Get property. Unlike Get, they permit arguments to be passed through the function. Member subroutines are useful for performing an action for each element within the collection class.

Outputting All Data

In the example file, a member subroutine is used within the Collection Class to print class data on the spreadsheet.

Full Collection Class

The following is the full class module in the example file.

Implementation

You’re likely very familiar with how to use the Item property to access specific elements within a collection class. When accessing collection class level properties, the Item property does not need to be used.

As a demonstration, the following is one of the procedures in the example file’s standard module.

In line 7, the regionPopulationChange property is accessed directly through the CountryDetails collection. If you recall from the “Inputting Bulk Calculations” section (above), a script within the property itself cycles through each element, preventing the need to access individual elements within the standard module.

The member subroutine Output_Country, which is used to print all collection class elements, is also accessed directly through the collection in a call statement (Line 9).

Other Notes

While not discussed in this post, it is possible to store member variables in a collection class. These member variables can be accessed through the Let and Get properties, as if the collection class is a standard class. This permits the collection to behave both as an object and as a collection. We briefly touched upon this in the post Creating a Collection Class within a Collection Class, where a child collection’s key is also stored as a member variable.

Leave a Reply