Using Custom Keys in Collection Classes

Accessing a specific object within a collection class can be a challenging endeavor. By assigning a proper custom key to objects in a collection class, you can avoid the often cumbersome error handling and loop searches often used to retrieve objects.

Note: If you came here looking for information on how to add custom keys to traditional VBA collections, see our post Creating and Using Collections in VBA.


This is the first post in the Tips and Tricks with VBA Classes series. This post will focus on creating and using custom keys for classes, presenting a straightforward means to access custom objects within a collection.

An object will automatically assign an index key once it is added to a collection. By default, the index key will always auto-increment to the next highest number in a collection. While the “Before” and “After” arguments can be used to insert objects at specific spots within a collection class, the entire collection will always increment from 1 to its total count.

Generally, the index key does not serve any purpose besides acting as a placeholder for collection items. In some cases, there may be a need to attach a more logical reference point to an item within a collection — this is where custom keys have an advantage.

Example File

This post’s example file uses a collection class which collects country data. Country data is added through an input form, and selecting a country’s name from the dropdown list will display details in the output. Macros must be enabled upon opening.


The example files stores country data in classes which are queried through a dropdown list.
The example files stores country data in classes which are queried through a dropdown list.

The Case for Custom Keys

Whenever you add a new item to a collection, it is automatically assigned a default index key, which is typically the next number in a sequence. The default index key assigned to new items in a collection does not serve a purpose beyond identifying an item’s place within the collection. Let’s say our example file only relied on index keys. If you were to add 5 European countries, say Netherlands, Denmark, Germany, Austria, and France (in that order), numeric index keys will be assigned in the sequence those countries are added. So Netherlands would be assigned 1, while France would be assigned 5. It’s manageable with a few countries, but what if you added all 195 countries of the world? What’s the best way of accessing a specific country when we don’t know its index key?

One solution is to loop through the collection with a nested conditional to test a property within the individual objects. The search may look like this when attempting to match an object by country name:

This search is somewhat processor intensive for simply finding a value. While other programming languages contain functions for searching collections, VBA requires a bit of a brute force approach which includes a loop and conditional. Wouldn’t it be easier if you could just include the country name as the key instead of a meaningless number? Well, this is possible with custom keys.

Determining the Best Custom Key Candidate

In most collection classes, at least one property (value) will be unique among all objects in the collection. This property might be a data point, a unique ID, or even a name. That unique identifier should also hold some meaning for the object its identifying, whether its specific name or smart coded identifier. Generally, you should think “If I were to run a search for this object, how would I find it?” That answer might be a good candidate for a custom key. The custom key value should never be duplicated in a collection.

In this post’s example file, we put together a collection class that records and displays information about countries of the world. It collects only a few data points — country name, population, land area, and also calculated population density.

Is it Unique?

While a country’s population and land area are likely unique by chance, the country name offers a truly unique value. Assuming no countries attempt to stake claim the same name, this value will always be unique.

Is it searchable?

While possible, it’s not particularly logical to search for countries based on population or land area. While it may work in some situations—such as searching for countries within certain population ranges—it’s not ideal if you’re looking for data on one specific country. In this example, we will search by country name, as the user needs to know they want data for a specific country.

Adding a Custom Key

A custom key is simple to add — it’s an optional parameter in the Collection Add function. For our example file, within the Insert subroutine in the CountryCollection collection class module, the key argument is added where the individual object is added (see line 10).

You may notice that the countryName parameter is added both as a key (line 10) and a assigned to a property (line 5). This is not required, but because we intend to access the country name in future data retrieval processes, it’s important to record it as a property as well. While a custom key value can be used to access a custom object in a collection, it cannot be read.

Retrieving Objects with Custom Keys

Before reviewing how to retrieve objects using custom keys, let’s look at how we would retrieve objects through the traditional index key.

This works by passing the index key through the parameter index (line 1). That key is then used to access a specific object by setting it as a new object (line 3), which is then returned from the function as a CountryGeneral type (individual object). In theory, this would also work for custom keys, except for one problem: custom keys are always of a String data type, even if they are numeric. Attempting to pass a custom key string through this property would return a data type mismatch error.

The following property would permit access to an object by using a custom key.

While the general procedure and parameter naming changed from the previous example, the only structural difference is that the parameter is assigned a String data type (line 1), as the argument passed will always be a string.

One of the great things about custom keys is that they will never overwrite the a collection’s index key. In fact, our example file uses the index key and custom key interchangeably. The index key is used to both to build a dropdown list picker and to iterate through the collection to check for duplicate values. The custom key is used to read object properties when a user queries a specific country. As done in the example file, both retrieval properties simply need to be included in the same class module.

Within the main coding module, we can now access a specific country simply by using the country name as a string.

And just to present how we continue to use the index key, the following are two snippets to search if a value already exists, and create the dropdown list.

While the premise of this post was to avoid looping through entire collections to save on processor resources, take note of this: we only loop through collections when an item is added. In the scope of this program, adding items could be considered a rare event. When querying items in the collection—considered a more frequent event—loops are not used.

Note: many VBA developers will use error handling to test if an item exists in a collection. I tend to avoid error handling at all costs (personal preference) given that users can modify error handling behavior through the VBA options menu.

Drawbacks of Custom Keys

There are a few important things to note if you intend to use custom keys in your projects.

  • Custom keys can only be of the String data type. If you attempt to add a numeric value as a custom key, it must be casted as a String, or else you will receive a type mismatch error.
  • Related to the previous point — given that custom keys are strings, you will not be able to iterate through them in a loop (loops will not accept numeric values if they are Strings). This is why our example file includes retrieval properties for both string and integer data types. You will need to revert back to using index keys whenever a collection must be used in a loop. Remember: index keys and custom keys can exist peacefully without interfering with eachother.

Other Notes

You may have noticed that I’ve taken liberty with the names of properties and subroutines in the class collection module. Almost all examples of class collection modules in training documents (including past posts on this site) use the standard subroutine Add and property Item to add and retrieve objects within a collection. Contrary to what it may seem, Add and Item are not intrinsic (native) procedures and can be called anything. Although I typically use the traditional names in simple class collections, I use more detailed names for class collections which include custom keys or any complex structures.

Leave a Reply