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 ...