Strongly Typed Collections in VBA

I'm an IntelliSense junkie.  I'm also a big believer in exposing errors as early as possible in the development process.  For those two reasons, it always bothered me that I couldn't declare a collection to be a specific type of object.  

A classic car collection

For the rest of this article, we'll be using the following simple class:

'VBA: the oVehicle class module
Option Compare Database
Option Explicit

Public Make As String
Public Model As String
Public Year As Integer

I'll use the following factory function to generate new instances of this class:

Function NewVehicleObject(Make As String, _
						  Model As String, _
                          Year As Integer) As oVehicle
    Set NewVehicleObject = New oVehicle
    With NewVehicleObject
        .Make = Make
        .Model = Model
        .Year = Year
    End With
End Function

Let's say we want to populate a dealer's lot inventory using this code.  Here's what it might look like:

Sub TestDealerLot()
    Dim Lot As New Collection  'of oVehicle objects
    Lot.Add NewVehicleObject("Ford", "Mustang", 1968)
    Lot.Add NewVehicleObject("Dodge", "Challenger", 1970)
    Lot.Add NewVehicleObject("Chevy", "Camaro", 1969)
    
    Dim Car As Object  'oVehicle
    For Each Car In Lot
        Debug.Print Car.Make, Car.Model, Car.Year
    Next Car
End Sub

What could go wrong?

This seems simple enough, but what if we accidentally add something other than an oVehicle object to our collection?  Such a mistake would not be caught at compile time.  For example, the following code will compile just fine, but it will raise a runtime error when we try to treat the string "Pontiac Road Runner" like an oVehicle object:

Sub TestDealerLot()
    Dim Lot As New Collection  'of oVehicle objects
    Lot.Add NewVehicleObject("Ford", "Mustang", 1968)
    Lot.Add NewVehicleObject("Dodge", "Challenger", 1970)
    Lot.Add NewVehicleObject("Chevy", "Camaro", 1969)
    Lot.Add "Pontiac Road Runner"  '<- this compiles...
    
    Dim Car As Object  'oVehicle
    For Each Car In Lot
        '...but it will break at runtime:
        Debug.Print Car.Make, Car.Model, Car.Year
    Next Car
End Sub

Compile errors are better than runtime errors

As a refresher, here's my list of programming errors in order from easiest/cheapest to fix to hardest/most expensive to fix.

1. Syntax errors
2. Compile errors
3. Misunderstood requirements (before you start writing code)
4. Automated test errors
5. Runtime errors
6. Misunderstood requirements (after you've written the code)
7. Logic errors

As you can see, you're much better off making mistakes at compile time than at runtime.

Exposing errors at compile time

How do you control whether an error appears at compile time rather than runtime?  One way is to avoid the use of Variant or generic Object types whenever possible.  

Creating Strongly-Typed Collection Classes

Instead of using the plain old Collection class built into VBA, you can create a custom collection class that will only accept a single type of object.  This technique blew my mind the first time I came across it.  

Read the article below for all the details necessary to create a "Strongly-Typed Collection."

How to Create a Strongly typed Collection in Vba

An Excel File with the sample code can be found here In this blog post I will show you how to create a Strongly Type Collection Class wich...

Productive BytesPosted byPierpaolo

Updating our example with better type safety

Using the article above for inspiration, we can create a custom class named "collVehicles" that accepts only oVehicle objects.  For now, I'll leave the creation of such a class as an exercise for the reader.

We can use this new strongly typed collection in place of the generic Collection object built in to VBA.  Now, if we make the same mistake of assigning a string to our collection, the VBA compiler warns us of our misdeed:

A better way?

The article above has all the information you need to manually create strongly-typed collection classes.  But it's a fair bit of work and most of the code is boilerplate.  That seems like the sort of thing we should be able to automate...

UPDATE: [2021-04-17] There is a better way...Strongly-Typed Collections: The Easy Way.

Image by Caden Vacek from Pixabay