Looping Through a Collection
Topic Last Modified: 2004-06-09
A CDO collection is always refreshed immediately following an Add or Delete operation on any of its members. This means that the collection's Count property is incremented or decremented, and all the members following the point of insertion or deletion are reindexed. To access one of these members, you must use its new index value. This is easy to forget if you are looping through the collection.
Consider this code fragment, intended to delete every member of a Messages collection:
Dim colMsgs As Messages ... size = colMsgs.Count For i = 1 to size colMsgs.Item(i).Delete Next i
When i = 1, the first Message object's Delete method is called and that message is deleted. The Messages collection's Count property is immediately decremented by one, and the Message object that had been the second message in the collection now becomes the first. Therefore, when i = 2, the second message in the reindexed collection, that is, the message that was originally third in the collection, is deleted, and the message that was originally fourth now becomes second.
The effect of this loop is to delete all the odd-numbered members of the original collection. Once i has been incremented past half the value of size, it becomes too large for the reindexed items, and a value of Nothing is returned for the remaining accesses.
The Microsoft® Visual Basic® For Each statement cannot be used as a workaround, because it is internally implemented as
For i = 1 to .Count .Item(i). Next i
which still exhibits the skipping behavior, although it does at least reread the Count property with each passage of the loop, so that it avoids going past the end of the collection.
A loop based on GetFirst and GetNext also encounters the same problem. The collection is reindexed following a Delete call, but the internal pointer is not decremented, so it now points to the member after the deleted member. When GetNext is called, it increments the pointer and returns the member following the desired one.
A safer approach is to delete the first message in every iteration. This loop has the effect intended by the erroneous code fragment:
size = colMsgs.Count For i = 1 to size colMsgs.Item(1).Delete Next i
This loop also processes the messages in forward order, that is, starting with the first and ending with the last. Of course, if you wish to delete everything in the collection, the simplest alternative is to use the Delete method of the Messages collection itself.
If you need to examine all the members of a collection and selectively delete certain ones, you can iterate backward to avoid the problem of skipping over the member following a deleted member. This code fragment loops backward without using
1, because Step is not available in VBScript:
size = colMsgs.Count For i = 1 to size Set objMsg = colMsgs.Item(size - i + 1) If objMsg.TimeExpired <= Now Then objMsg.Delete End If Next i
Some collections, such as Columns, allow a new member to be inserted following a specified existing member instead of being added at the end. If you insert a Column object into the middle of the collection using its Add method, you must take into account the new Index values of the columns that come after the new column.
However, the Add method of any sortable collection can effectively insert a new member before the end. An AddressEntries collection, for example, is normally sorted on the Name property. If you call Add with a display name lying somewhere within the alphabet, the AddressEntry objects that follow it alphabetically are all reindexed.