Strongly Typed Collections in VBA
Get better type safety and access to IntelliSense inside For Each loops by using "strongly-typed collections" in place of the VBA Collection type.
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:
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.
- Syntax errors
- Compile errors
- Misunderstood requirements (before you start writing code)
- Automated test errors
- Runtime errors
- Misunderstood requirements (after you've written the code)
- 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."
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