Share this Page URL

Collections > Collections - Pg. 423

Collections have a couple of useful read-only properties that help you iterate (loop) through the list of objects: · The Count property returns the number of objects in the collection. · The Item property returns a specified object from the collection. These properties can be used in a For Next loop, a construct that traverses the objects in a collection over a range that you specify. In older versions of VBA, the only way to iterate through a collection was by using the For Next construct with a counter -- a variable used to keep track of where you are in the collection -- and a variable to hold the object reference returned by Item . The code in Example 18-24 illustrates this technique. Example 18-24. Using a Counter to Track Collection Position Dim objDoc As Document For intCounter = 1 To Documents.Count Set objDoc = Documents.Item(intCounter) ' Display the document's filename MsgBox objDoc.Name Next Some things to note about this code: · The For...To statement defines the range over which you want to iterate. In this case, we want to go from the first object (at index 1) to the last object. · We use the Set keyword to fill our object variable with a value from the collec- tion. We need to tell the collection which item we'd like back. We use the loop counter for this. · Once we've filled our object variable, we can use it. In this case, we just display a recipient's name. · We don't set the object variable to Nothing after using it. This is because the object is part of the collection, and we don't want to delete it. · We end the body of the loop with a Next statement. In more recent versions of VBA, there is another, easier way to loop through an entire collection. The For Each...Next construct does a lot of the legwork of looping for you. You don't need to declare a counter, and the Set operation is done under the covers for you. We could rewrite the previously example to the one shown in Ex- ample 18-25. Example 18-25. Using For Each...Next to Loop Through Collections For Each objDoc In Documents MsgBox objDoc.Name Next Working with VBA A VBA Language Primer | 423