Programming Excel with VBA and .NET

Get Objects from Collections

Excel members like Workbooks, Worksheets, Charts, Sheets, and Range return groups of objects called collections. Collections are special because they provide a hidden enumerator method that lets you use them with the For Each statement as well as Item and Count methods that let you get specific objects from the group.

In Excel, collections are usually (but not always) named as the plural form of the objects they contain: so the Workbooks collection contains Workbook objects, the Worksheets collection contains Worksheet objects, and so on. There are some obvious exceptions: Sheets contains various types of sheet objects, and Range contains other Range objects, each of which contains a single cell. The Range collection is definitely weird, but Excel has no Cell object so that’s just the way things work!

In Excel, you get collections using a property from the collection’s parent object. The property usually has the same name as the returned collection, which can make using Help a little frustrating (Figure 4-7).

Figure 4-7. Pressing F1 on Workbooks displays the Workbooks property, not the Workbooks collection you might expect!

To see Help on the collection, including a list of its members, click the link for the collection object on the property Help topic. Figure 4-8 shows the Help for the Workbooks collection object.

The graphic in Figure 4-8 shows how you navigate from the Application object to the Workbook object. You can interpret that graphic as saying “Use the Application object’s Workbooks property to get the Workbooks collection, which contains Workbook objects, from which you can use other properties to get other objects.” You can see why they used a graphic instead of words! If you click on any of the boxes in the graphic, you’ll get Help on that object. If you click on the Multiple Objects box, you’ll see a list of the objects you can get from the Workbook object (Figure 4-9).

Figure 4-8. It takes an extra click to get Help on collections

You often need to use Help to figure out how to navigate to the object you need. Excel’s object library is complicated, as shown by Figure 4-10. Knowing how to navigate it is one of the key skills you must develop as an Excel programmer.

In fact, Figure 4-10 cheats by using shortcut methods like Application.Range to simplify the hierarchy. The real hierarchy is Application → Workbooks → Workbook → Worksheets → Worksheet → Range, but that really wouldn’t fit!

You get specific objects from a collection using the collection’s Item property:

    ' Show the name of the first worksheet.
    Debug.Print Application.Workbooks.Item(1).Worksheets.Item(1).Name

Wait! That’s not the way it’s usually shown. You can omit Item because it is the default property of the collection. You can also omit Application.Workbooks since Worksheets is a global method. The way you’d usually write that code is this:

    ' Show the name of the first worksheet (simplified)
    Debug.Print Worksheets(1).Name

Figure 4-9. Click on the graphic to navigate to other objects in Help

Figure 4-10. Excel’s object hierarchy doesn’t fit on one Help screen (look at the scrollbar)

Or you can use the collection with For Each to show a list of all worksheets:

    Dim ws As Worksheet
    ' Show names of all worksheets.
    For Each ws In Worksheets
        Debug.Print ws.Name
    Next

Tip

Most collections have two types of indexes . The first type is numeric (Worksheets(1)), and the second type uses the item’s name (Worksheets("Sheet1")).

Collections are also usually the way you create new objects in Excel—most collections provide an Add method for creating new instances of objects and adding them to the collection. Interestingly, you usually delete items from Excel collections using the individual object’s Delete method. The following code illustrates adding and deleting a worksheet:

    ' Create a new workhseet
    Set ws = Worksheets.Add
    ' Delete that sheet
    ws.Delete

Table 4-2 lists the members that are common to most collections .

Table 4-2. Common members for collection objects

Member	Use to	Example
Add	Create a new object and add it to the collection.	Workbooks.Add
Count	Get the number of objects in the collection.	' Alternative to For Each For i = 1 to Sheets.Count Debug.Print Sheets(i).Name Next
Item	Get an object from the collection. This member name is usually omitted since it is the default member.	Worksheets("Objects").UsedRange.AutoFormat