Collection Objects

In programming with the Excel object model (or indeed any object model), it is common to have a great many objects "alive" at the same time. For instance, each cell within the current selection is an object (a Range object), as is each row and column in each open worksheet. Hence, at any given time, there are thousands of objects in existence. To manage these objects, the designers of an object model generally include a special type of object called a collection object.

As the name implies, collection objects represent collections of objects—generally objects of a single type. For instance, the Excel object model has a collection object called Rows that represents the set of all rows in the worksheet in question (as Range objects). It is customary to say that the Rows collection object contains the rows in the sheet, so we will use this terminology as well. There is one Rows collection for each open worksheet.

Collection objects are generally just called collections, but it is very important to remember that a collection is just a special type of object. As we will see, the properties and methods of a Collection object are specifically designed to manage the collection.

We can generally spot a collection object by the fact that its name is the plural of the name of the objects contained within the collection. For instance, the Worksheets collection contains Worksheet objects. However, in some cases, this naming convention is not followed. For instance, the Rows collection contains Range objects. In the Excel object model, there are no Cell, Row, or Column objects. These are all represented by Range objects. We will devote an entire chapter (Chapter 19) to the important Range object.

Collections are extremely common in the Office object models. In fact, almost one-half of all of the objects in the Excel object model are collections! Table 9-1 shows some of the more commonly used collections in the Excel object model.

Table 9-1. Some Excel Collection Objects

Areas

FormatConditions

SeriesCollection

Axes

LegendEntries

Sheets

Borders

Names

Windows

ChartObjects

PivotFields

Workbooks

Charts

PivotTables

Worksheets

DataLabels

Points

Filters

Range

We emphasize the fact that a collection is just a special type of object. Indeed, the properties and methods of a Collection object are specifically designed to manage the collection. Accordingly, the basic requirements for a collection object are:

• A property called Count that returns the number of objects in the collection. This is a read-only property; that is, it cannot be set by the programmer. It is automatically updated by VBA itself.

• A method called Add (or something similar, such as AddNew) that allows the programmer to add a new object to the collection.

• A method called Remove, Close, or Delete, or something similar, that allows the programmer to remove an object from the collection.

• A method called Item that permits the programmer to access any particular object in the collection. The item is usually identified either by name or by an index number.

Note that these basic requirements are not hard and fast. Some collection objects may not implement all of these members, and many implement additional members. For instance, the Areas and Borders collections do not have an Add method, since we are not allowed to add objects to these collections. We can only manipulate the properties of these collections.

Some Excel collections are considerably more complicated than others, since they have several properties and methods that relate specifically to the type of object they contain. For instance, the Sheets collection has 10 properties and 8 methods. Several of these members, such as the PrintOut method, are included specifically so that they can operate on all of the sheets in the collection at the same time. (A sheet is either a worksheet or a chartsheet.)

0 0

Post a comment