Excel VBA Compile Error Argument Not optional while iterating through array attached to a class collection

Question

I keep getting the error "Compile Error: Argument not optional" while looping through the Team/Group arrays attached to the class Event stored in a collection

It gives a compile error at lines as it is not treating the object as Array:

For ctr = LBound(evt.Team) To UBound(evt.Team)
For ctr = LBound(evt.Group) To UBound(evt.Group)

But when i directly print it referencing the index it works... like this line:

Debug.Print evt.Team(0)
Debug.Print evt.Group(1)

My requirement is simple.

1. Read the data from the Excel Data and store each event in a collection for further processing
2. Team and Group arrays are added to the class Event which are storing the team details and their respective applicable(ticked as "Y") groups only

So far this works fine and the data is stored in the Event Collection properly.

The problem arises when i try to iterate the collection and try looping through the Team and Group arrays.

Events Module Code

Option Explicit

Dim ws As Excel.Worksheet
Dim colEvents As Collection
Dim hdrRow1, hdrRow2, startRow, startCol, lastRow, lastCol
Dim startTeam, endTeam, startGroup, endGroup
Dim ctr, ctrRow, ctrCol

Sub Main()
    Set ws = ThisWorkbook.Sheets("Events")
    Set colEvents = New Collection

With ws
    lastRow = (.UsedRange.Rows.Count)
    lastCol = (.UsedRange.Columns.Count)
    'Debug.Print "Last Row: " & lastRow & " - " & "Last Column: " & lastCol

    hdrRow1 = 1
    hdrRow2 = 2
    startRow = 3
    startCol = 1
    startTeam = 4
    endTeam = 6
    startGroup = 7
    endGroup = 11

    'Get Groups
    For ctrRow = startRow To lastRow
        Dim oEvent As clsEvent
        Set oEvent = New clsEvent

        'Get No, Name, Type
        oEvent.No = .Cells(ctrRow, startCol)
        oEvent.Name = .Cells(ctrRow, startCol + 1)
        oEvent.EType = .Cells(ctrRow, startCol + 2)

        'Get Team Details
        ctr = 0
        For ctrCol = startTeam To endTeam
            oEvent.Team(ctr) = .Cells(ctrRow, ctrCol).Value
            ctr = ctr + 1
        Next

        'Get Group Details
        ctr = 0
        For ctrCol = startGroup To endGroup
            If (.Cells(ctrRow, ctrCol).Value = "Y") Then
                oEvent.Group(ctr) = .Cells(hdrRow2, ctrCol).Value
                ctr = ctr + 1
            End If
        Next

        colEvents.Add oEvent
    Next
End With

'Check Collection
Dim evt As clsEvent
For Each evt In colEvents
    Debug.Print "No: " & evt.No
    Debug.Print "Name: " & evt.Name
    Debug.Print "Type: " & evt.EType

    'Loop through Team array
    Debug.Print "Team Details: "
    For ctr = LBound(evt.Team) To UBound(evt.Team)
        Debug.Print evt.Team(ctr)
    Next

    'Loop through Group array
    Debug.Print "Group Details"
    For ctr = LBound(evt.Group) To UBound(evt.Group)
        Debug.Print evt.Group(ctr)
    Next
Next
End Sub

Class Module Event

Option Explicit

Private pNo As Integer
Private pName As String
Private pEType As String
Private pTeam(2) As Integer
Private pGroup() As String

'Prop No
Public Property Get No() As Integer
    No = pNo
End Property

Public Property Let No(ByVal vNewValue As Integer)
    pNo = vNewValue
End Property

'Prop Events
Public Property Get Name() As String
    Name = pName
End Property

Public Property Let Name(ByVal vNewValue As String)
    pName = vNewValue
End Property

'Prop Event Type
Public Property Get EType() As String
    EType = pEType
End Property

Public Property Let EType(ByVal vNewValue As String)
    pEType = vNewValue
End Property

'Prop Type
Public Property Get Team(ByVal index As Long) As Integer
    Team = pTeam(index)
End Property

Public Property Let Team(ByVal index As Long, ByVal vNewValue As Integer)
    pTeam(index) = vNewValue
End Property
'Prop Group
Public Property Get Group(ByVal index As Long) As String
    Group = pGroup(index)
End Property

Public Property Let Group(ByVal index As Long, ByVal vNewValue As String)
    If (Not pGroup) = -1 Then
        ReDim Preserve pGroup(0)
    End If
    If (index > UBound(pGroup)) Then ReDim Preserve pGroup(index)
    pGroup(index) = vNewValue
End Property

Private Sub Class_Initialize()
    'statements
End Sub

Private Sub Class_Terminate()
    'statements
End Sub

Answer 1

Team isn't an array, that's why VBA isn't treating it as one.

Team (and Group) are indexed properties. They're abstracting away the fact that the encapsulated data is stored in an array. To the calling code, the data might as well be stored in a Collection, a Dictionary, an ArrayList, a HashSet ....it makes no difference at all: given an index, the property is able to retrieve and return an item.

Typically, you expose indexed properties on a custom collection class - and you expose such a property along with other members such as Count, Add, and Clear.

But this isn't a custom collection class.

So, a solution could be to expose a TeamCount and a GroupCount property. Note that exposing UBound(pTeam) or UBound(pGroup) would make your abtraction a leaky one, and could cause problems if you later decide that you'd rather have a Collection to hold the encapsulated data.

Public Property Get TeamCount() As Long
    TeamCount = UBound(pTeam) + 1 '<~ array is zero-based, so count is +1
End Property

Public Property Get GroupCount() As Long
    If UBound(pGroup) >= 0 Then '<~ would be -1 if uninitialized
        GroupCount = UBound(pGroup) + 1
    End If
End Property

Then you can do:

For ctr = 0 To evt.TeamCount - 1

Note that this is still a leaky abstraction: the zero-based nature of the encapsulated array is bleeding everywhere.

A better abstraction would enable this to work:

For Each t In evt.Teams

There are several ways to achieve this - here's the easiest (and probably the least performant):

Public Property Get Teams() As Collection
    Dim result As Collection
    Set result = New Collection
    Dim i As Long
    For i = LBound(pTeams) To UBound(pTeams)
        result.Add pTeams(i)
    Next
    Set Teams = result
End Property