Page 58 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 33

Session 3 — The Excel Object Model 33

needs to work with one of its existing worksheets. At other times the object does not exist,
and your program must create it as well as obtain a reference — for example, when you add
a new worksheet to a workbook. Both of these techniques are explained throughout the
book for the various Excel objects with which you’ll be working.

Working with Collections
The Excel object model makes frequent use of collections. This is a special class (the
Collection class) that is designed specifically for keeping track of multiple instances
of other objects. Whenever there is the potential for more than one copy of an object to
exist, Excel almost always uses a collection.
Here’s an example. An Excel workbook can contain multiple worksheets. This is handled
as follows:
The workbook is represented by a Workbook object.
The Workbook object has a Sheets collection.
The Sheets collection contains one Sheet object for each sheet in the workbook.

Why is the collection containing worksheets called Sheets instead of
Worksheets? This is because Excel has both worksheets, which contain rows
Note and columns of data as well as embedded charts, and chart sheets, which
contain only a single chart. Thus, the Sheets collection contains Sheet
objects, which come in two types: worksheets and chart sheets. If you want
to obtain only worksheets or only chart sheets, use the Worksheets or
Charts properties (as described later in this session).

This example illustrates the convention used for naming collections as the plural of the
object contained — the Sheets collection contains Sheet objects, the Windows collection
contains Window objects, and so on. There are very few exceptions to this rule, which will be
pointed out throughout the book.
A Collection object has the Count property, Count that gives the number of objects in
the collection. This is a read-only property — you can read but not change its value.
There are two ways to refer to an object in a collection. One is by its numerical position
within the collection; the other is by the object’s unique key. The key for a collection
depends on the object it contains and is usually the piece of information that identifies an
object. In the Sheets collection, for example, the unique key is the sheet’s name. Thus,
Sheets(1)

references the first sheet in the current workbook, while
Sheets(“SalesData”)
references the sheet named “SalesData.” This latter method is more useful because you
usually do not know the position of an object in a collection.