How do I correctly work with a collection of VBA Class Modules?

Question

I want to create a collection of airports.

An airport has many parameters, but for the sake of simplicity, let's assume an airport class is defined like this in Airport Class Module:

'Class Airport
 Public name As String ' Stores name of the airport
 Public flights As Long ' Stores number of flights in that airport

Then my module is pretty big but this is the part where I read columns from an Excel file and store values in airports collection, removing duplicate ones:

Dim airports As Collection
Set airports = New Collection

'Putting airports in collection
Dim c As Range

For Each c In wsToCheck.Range("D:D")
    
    On Error Resume Next
    
    Dim airport As New Airport
    
    airport.name = c.Value
    airports.Add airport, c.Value
    
    On Error GoTo 0
    
Next

If I do in the middle

Debug.Print airport.name

I get the name, but when I do

Debug.Print airports(1).name

Nothing is printed (but no error neither).

I was using a collection of strings before and it was working. But I need more than one string per airport now.

What is the wrong in my code? Am I using collections right?

Answer 1

You have two problems with your code.

The first is that are probably creating a Collection with millions of items because the range you're iterating over is all of column D (D:D). This needs to be bound.

The second issue is your variable name airport is the exact same name as your class Airport. This can easily confuse VBA, so you need to choose a different name for one of them.

Here's an example that works:

Option Explicit

Sub test()
    Dim wsToCheck As Worksheet
    Set wsToCheck = ThisWorkbook.Sheets("Sheet1")

    Dim airportNames As Range
    Set airportNames = wsToCheck.Range("D1:D10")

    Dim airports As Collection
    Set airports = New Collection

    'Putting airports in collection
    Dim i As Long
    Dim c As Range
    For Each c In airportNames
        Dim thisAirport As Airport
        'Debug.Print c.Address & "=" & c.Value
        Set thisAirport = New Airport
        thisAirport.name = c.Value
        thisAirport.flights = i
        i = i + 1
        airports.Add thisAirport
    Next

    'now print them out
    For i = 1 To airports.Count
        Debug.Print airports(i).name & ", " & airports(i).flights
    Next i

End Sub