Collections

Although collections and the objects they contain, such as the Workbooks collection and the Workbook object are distinct objects each with their own properties and methods, they're grouped as one unit in most object model graphics to reduce complexity.

To return a single member of a collection, you usually use the Item property or method and pass the name or index number of the member as the index argument. For example, in Excel, the following expression returns a reference to an open workbook by passing its name "Sales.xls" to the Item property and then invokes the Close method to close it:

Workbooks.Item("Sales.xls").Close

The Item property or method is the default for most collections, so you can usually omit it from your expression. For example, in Excel, the following two expressions are equivalent:

Workbooks.Item("Sales.xls")

-or-

Workbooks("Sales.xls")

To reference items in a collection by using an index number, simply pass the number of the item to the Item property or method of the collection. For example, if Sales.xls is the second workbook in the Workbooks collection, the following expression will return a reference to it:

Workbooks(2)

Note   Most collections used in Office applications (except Access) are one-based, that is, the index number of the first item in the collection is 1. However, the collections in Access and some components, such as ADO and DAO, are zero-based, which is, the index number of the first item is 0. For more information, refer to the Visual Basic Reference Help topic for the collection you want to work with.

Adding Objects to a Collection

You can also create new objects and add them to a collection, usually by using the Add method of that collection. The following code fragment creates a new document by using the Professional Memo.dot template and assigns it to the object variable docNew:

Const TEMPLATE_PATH   As String = "c:\program files\microsoft office\templates\1033\"
Dim docNew            As Word.Document

Set docNew = Documents.Add(Template:=TEMPLATE_PATH & "memos\professional memo.dot")

Working with Objects in a Collection

You can find out how many objects there are in a collection by using the Count property. The following Excel example displays a message box with the number of workbooks that are open:

MsgBox Workbooks.Count & " workbooks are open."

You can perform an operation on all the objects in a collection, or you can set or test a value for all the objects in a collection. To do this, you use a For EachNext structure, or a ForNext structure in conjunction with the Count property to loop through all the objects in the collection.

Whenever possible, you should use a For EachNext loop when you need to work with all the items in a collection. A For EachNext loop generally performs faster and doesn't require you to use or test a loop counter, which can introduce errors. The following Excel example contains a For EachNext structure that loops through the Worksheets collection of a workbook and appends " - By Automation" to the name of each worksheet:

Sub CreateExcelObjects()
   Dim xlApp            As Excel.Application
   Dim wkbNewBook       As Excel.Workbook
   Dim wksSheet         As Excel.Worksheet
   Dim strBookName      As String
   
   ' Create new hidden instance of Excel.
   Set xlApp = New Excel.Application
   ' Add new workbook to Workbooks collection.
   Set wkbNewBook = xlApp.Workbooks.Add
   ' Specify path to save workbook.
   strBookName = "c:\my documents\xlautomation.xls"
   ' Loop through each worksheet and append " - By Automation" to the
   ' name of each sheet. Close and save workbook to specified path.
   With wkbNewBook
      For Each wksSheet In .Worksheets
         wksSheet.Name = wksSheet.Name & " - By Automation"
      Next wksSheet
      .Close SaveChanges:=True, FileName:=strBookName
   End With

   Set wkbNewBook = Nothing
   Set xlApp = Nothing
End Sub

Under some circumstances, you must use a ForNext loop to work with items in a collection. For example, if you try to use a For EachNext loop to delete all the objects in a collection, only every other object in the collection will be deleted. This is because after deleting the first item, all items in the collection are re-indexed so that what was the second item is now the first. When the Next statement runs at the end of the first execution of the loop, the pointer is advanced one, skipping that item for the next iteration of the loop. For this reason, to delete all items in a collection, you must use a ForNext loop that starts from the end of the collection and works backwards.

Another situation that requires you to use a ForNext loop to work with items in a collection is if you need to work with only a specific number of items, say the first ten, or every tenth item.

See Also

Objects, Collections, and Object Models: Technology Backgrounder | Objects Exposed by an Object Model | Properties and Methods | Events | Using the Object Browser | Working with the Outlook Object Model