A correctly implemented collection object allows you to access the members of the collection either by key name or by ordinal position in the collection. This is handled by the Item method, which accepts a variant as its single parameter. If the parameter is a string value, Item tries to match the parameter with a key in the collection. On the other hand, if the parameter is numeric (including the string representation of a number), the Item method uses this number to return the item in that ordinal position. This is demonstrated by the following snippets:
sEmployeeName = adoRecordset.Fields.Item(l)
sEmployeeName = adoRecordset.Fields(l)
Collection Objects 83
sEmployeeName = adoRecordset.Fields("Empname")
You will also note from the above snippets that because Item is the default method of the collection object, it can be called implicitly.
You can iterate through a collection by obtaining its Count property value, which returns the number of items in the collection, and executing a For...Next loop, each time setting an object variable to the new collection member, as follows:
For i = 1 To oColl.Count
Set oCollMember = oColl.Item(i)
'do some stuff with oCollMember Set oCollMember = Nothing
However, VB provides a more efficient method: the For Each...Next loop. The For Each...Next loop iterates through the collection, automatically assigning a reference to the current member of the collection, then exiting once the end of the collection has been reached, as this rewritten snippet shows:
For Each oCollMember in oColl
'do some stuff with oCollMember
Using For Each...Next is also safer because collection members can be automatically reindexed by the actions of another part of the program—for example, if a member is removed from the collection—which means that stored index numbers shouldn't relied upon.
For more information about the Collection object and the For Each...Next statement, see their entries in Chapter 7.
Was this article helpful?