Excel members like
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
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
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).
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
Range, but that really wouldn’t fit!
You get specific objects from a collection using the collection’s
' 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
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
Dim ws As Worksheet ' Show names of all worksheets. For Each ws In Worksheets Debug.Print ws.Name Next
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
Create a new object and add it to the collection.
Get the number of objects in the collection.
' Alternative to For Each For i = 1 to Sheets.Count Debug.Print Sheets(i).Name Next
Get an object from the collection. This member name is usually omitted since it is the default member.