Making Custom Collection in Excel VBA

Question

I'm working on making a customer collection class that contains functions for if it contains an object, or if you want to remove a specific object. But I'm wondering if there is a way to call an object at a location though.

Here is an example:

If I want the 2nd object in a normal collection, I'd do this coll(2) and I'd get the 2nd object.

How can I use similar nomenclature for a custom class? Here is my custom class.

Option Explicit

Private Const modName = "CollectionClass"

Private zCollection As New Collection

Property Get coll() As Collection
    Set coll = zCollection
End Property

Public Function Count() As Variant

10      On Error GoTo SUB_ERR

20      Count = zCollection.Count

SUB_EXIT:
30      Exit Function

SUB_ERR:
40      ProcessError errorNumber:=Err.Number, _
                     errorDescription:=Err.Description, _
                     errorLine:=Erl, _
                     errorInRoutine:=modName & ": Count"

End Function

Public Sub Add(var As Variant)

10      On Error GoTo SUB_ERR

20      zCollection.Add var

SUB_EXIT:
30      Exit Sub

SUB_ERR:
40      ProcessError errorNumber:=Err.Number, _
                     errorDescription:=Err.Description, _
                     errorLine:=Erl, _
                     errorInRoutine:=modName & ": Add"

End Sub

Public Sub Remove(loc As Long)

10      On Error GoTo SUB_ERR

20      zCollection.Remove loc

SUB_EXIT:
30      Exit Sub

SUB_ERR:
40      ProcessError errorNumber:=Err.Number, _
                     errorDescription:=Err.Description, _
                     errorLine:=Erl, _
                     errorInRoutine:=modName & ": Remove"

End Sub

Public Sub RemoveObj(var As Variant)

10      On Error GoTo SUB_ERR

20      Dim i As Long

30      If IsMissing(var) Then
40          Dim xxx As Integer: xxx = 1000000 'Errors if no var sent
50      Else
60          Select Case TypeName(var)
                Case "PartClass"
70                  Dim part As PartClass
80                  i = 1
90                  For Each part In zCollection
100                     If part Is var Then
110                         Me.Remove i
120                         Exit Sub
130                     End If
140                     i = i + 1
150                 Next
160             Case Else
170                 xxx = 1000000 'Errors if unknown Type Name
180         End Select
190     End If

SUB_EXIT:
200     Exit Sub

SUB_ERR:
210     ProcessError errorNumber:=Err.Number, _
                     errorDescription:=Err.Description, _
                     errorLine:=Erl, _
                     errorInRoutine:=modName & ": RemoveObj"

End Sub

Public Function Contains(var As Variant) As Boolean

10      On Error GoTo FUNC_ERR

30      If IsMissing(var) Then
40          Dim xxx As Integer: xxx = 1000000 'Errors if no var sent
50      Else
60          Select Case TypeName(var)
                Case "PartClass"
70                  Dim part As PartClass
90                  For Each part In zCollection
100                     If part Is var Then
110                         Contains = True
120                         Exit Function
130                     End If
150                 Next
160             Case Else
170                 xxx = 1000000 'Errors if unknown Type Name
180         End Select
190     End If

FUNC_EXIT:
200     Exit Function

FUNC_ERR:
210     ProcessError errorNumber:=Err.Number, _
                     errorDescription:=Err.Description, _
                     errorLine:=Erl, _
                     errorInRoutine:=modName & ": Contains"

End Function

So if I have an instance of my custom collection custColl, currently, I have to use this to get the 2nd object custColl.Coll(2), but I'm wondering if there is a way to make it so I can just do custColl(2)

Thank you for the help! Jason

Answer 1

Not possible, because custColl(2) could only be used to reference the 2nd custom collection in an array of custom collections.