Inspecting elements of a collection

[confusion123]

The code below is taken from here:

VBA Class Modules - The Ultimate Guide - Excel Macro Mastery


standard module

Sub CreateReport()
 
    Dim coll As Collection
    ' read the data
    Set coll = ReadAlbums(1990, 2001)
     
    ' Print the album details
    PrintAlbum coll
 
    ' Print the total sales
    PrintTotalSales coll
     
End Sub
 
Function ReadAlbums(startYear As Long, endYear As Long) _
              As Collection
     
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
     
    ' Create a collection to store the albums
    Dim coll As New Collection
    Dim oAlbum As ClsAlbum
     
    Dim i As Long, Year As Long
    For i = 2 To rg.Rows.Count
         
        Year = rg.Cells(i, 3)
        If startYear <= Year And endYear >= Year Then
            ' Create new album
            Set oAlbum = New ClsAlbum
            ' Add the details
            oAlbum.Artist = rg.Cells(i, 1)
            oAlbum.Title = rg.Cells(i, 2)
            oAlbum.Year = Year
            oAlbum.Genre = rg.Cells(i, 4)
            oAlbum.Sales = rg.Cells(i, 5)
            ' Add the album objecdt to the collection
            coll.Add oAlbum
        End If
         
    Next I
     
    Set ReadAlbums = coll
     
End Function  ' PUT BREAK POINT HERE
 
Sub PrintAlbum(coll As Collection)
     
    Dim oAlbum As ClsAlbum
    For Each oAlbum In coll
        Debug.Print oAlbum.Title, oAlbum.Artist
    Next
     
End Sub
 
Sub PrintTotalSales(coll As Collection)
     
    Dim oAlbum As ClsAlbum, Sales As Double
    For Each oAlbum In coll
        Sales = Sales + oAlbum.Sales
    Next
     
    Debug.Print "Total number sales is " & Sales
     
End Sub

ClsAlbum

Option Explicit
' clsAlbum class module
Private m_sArtist As String
Private m_sTitle As String
Private m_sYear As String
Private m_sGenre As String
Private m_sSales As String
 
' Properties
Public Property Get Artist() As String
    Artist = m_sArtist
End Property
Public Property Let Artist(ByVal sArtist As String)
    m_sArtist = sArtist
End Property
 
 
 Public Property Get Title() As String
    Title = m_sTitle
End Property
Public Property Let Title(ByVal sTitle As String)
    m_sTitle = sTitle
End Property
 
 
 Public Property Get Year() As String
    Year = m_sYear
End Property
Public Property Let Year(ByVal sYear As String)
    m_sYear = sYear
End Property
 
 
 
 Public Property Get Genre() As String
    Genre = m_sGenre
End Property
Public Property Let Genre(ByVal sGenre As String)
    m_sGenre = sGenre
End Property
 
 
 Public Property Get Sales() As String
    Sales = m_sSales
End Property
Public Property Let Sales(ByVal sSales As String)
    m_sSales = sSales
End Property

What I can understand is if I put a break point here:

End Function

then try to inspect the elements of the collection by typing:

Coll(1)

in the immediate window, get an error message:

Object doesn't support this object or method

but I can clearly see the items of the collection if I go to the locals window.

What am I doing wrong?

 

[korhan]

You can type coll(1).Artist or coll.Item(1).Artist to inspect a certain item in the collection.

 

[shg]

In the immediate window, you need a question mark as the shortcut to Debug.Print:

? Coll(1)

 

[confusion123]

Definately tried with ? but can't be 100% sure I tried the first method.

Will try again tomorrow to see.

Thanks for the time being.