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?
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