How to get object's position within Collection?

Question

Prerequisites:

I've got:

• a Collection called "users"
• which consits of instances of a Class called "MatchClass"
• the "MatchClass" consists of only two properties - userid and matchArray (plus some methods which are not relevant to the question)

---

What I want to do

Is to loop over the Collection, while simultaneously being able to

• access the position within the collection
• access the properties of the class instance
• find the highest value out of all matchArrays (they are already sorted) and store the userid

---

What I've tried so far:

To loop over the Collection with a For Each loop

Dim users as New Collection
Dim i as Byte
Dim max as Integer: max = 0
Dim maxuser as String
' users Collection is  filled with instances of MatchClass 
' ... skipping code to simplify ...
Dim user as MatchClass
For Each user in users
        temp = user.matchArray(0, 0)
        If temp > max Then
            max = temp
            maxuser = users.Item ' <- this won't work
        End If
Next user

Then I can't acess the position of the user within the For Each loop

Alternatively I tried numerical looping:

For i = users.Count to 1 Step -1
   users.Item(i).matchArray(0, 0) ' <- this won't work
Next i

This unfortunately won't work either, as I can't access the properties of the MatchClass instances via the users.Item(i) code either.

---

Is there perhaps a best of both worlds solution where I can do both? Feels like it should be somethign arbitrary that I'm just missing.

Answer 1

As per my comment, I would've expected either of these methods to work. Happy to be proven wrong though because they are untested ...

For Each

Dim users As New Collection
Dim i As Byte
Dim max As Integer: max = 0
Dim maxuser As String
' users Collection is  filled with instances of MatchClass
' ... skipping code to simplify ...
Dim user As MatchClass

Dim lngIndex As Long

For Each user In users
        lngIndex = lngIndex + 1            
        temp = user.matchArray(0, 0)

        If temp > max Then
            max = temp
            maxuser = lngIndex
        End If
Next user

For

Dim user As MatchClass

For i = users.Count To 1 Step -1
   Set user = users.item(i)
   matchArray = item.matchArray(0, 0)
Next i

If I've understood your question then I would’ve thought either of the above (if not both) would get you your desired result.

Answer 2

• If MatchClass is an object and users is a collection of MatchClass instances, then you should use Set keyword to assign an object reference (I'm referring to this line maxuser = users.Item ' <- this won't work -- but there is another problem with this line (mentioned below)).
• When you loop through a collection (in VBA), you are looping through its items. You do not have "read" access to its keys. So Dim maxuser as String doesn't make sense, unless you have a collection of strings, which you don't.
• The Item member of the Collection object requires either an index or the key that was originally used to add the item to the collection.

I cannot test this (so I might be wrong myself), but my view is:

Private Sub FindMaxUser()
    Dim users As New Collection

    Dim i As Byte
    Dim max As Long: max = 0

    Dim temp As Long
    ' ... skipping code to simplify ...

    Dim user As MatchClass
    Dim maxuser As MatchClass ' <- Shouldn't be a string.

    For Each user In users
        temp = user.matchArray(0, 0)
        If temp > max Then
            max = temp
            Set maxuser = user ' <- MatchClass is an object. "Set" keyword is required for object references
        End If

    Next user
End Sub

Key changes here are to declare maxuser as MatchClass and using Set inside the loop (when a new maximum value is found).