Page 134 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

An object that contains multiple objects of the same type is a collection, and each object
within the collection is a member of the collection. To refer to a particular member of a col­
lection, you can use either its index number or its name. In the preceding example, we used
the Worksheets collection and referred to the particular worksheet named Sheet2.

Introducing the Application Object

The Application object is the highest object within the Microsoft Excel Object Model. The
Application object contains all of the properties and methods to fully manipulate the Excel
application, along with the objects that represent individual workbooks and the data they
contain.
Because it is the topmost object within the object model, logically you would need to begin
all references with the Application object. To refer to the first cell in a worksheet (A1), you
would need to start at the Application object, go to the Worksheet object, and then select the
Cell object. To set the first cell equal to 100, the VBA code would be as follows:

Application.Workbooks(1).Worksheets(1).Cells(1,1) = 100
Lengthy sequences in dot notation are cumbersome to use, so the Excel programmers did
expose some of the more common objects directly, such as workbooks, worksheets, and cells,
without the code having to go through the Application object. Care must be taken, especially
when working with cells directly, that you have selected the proper workbook and worksheet.
If you’re sure you have selected the proper workbook and worksheet, such as by using the
Worksheet object’s Activate method described in the next section of this chapter, you could
abbreviate the previous command to Cells(1,1) = 100.

Properties

Working with the Application object will usually involve reading or setting one of its many
properties. The Application object contains more than 170 properties that let you control
every aspect of the Excel application. From workbooks and worksheets to columns and
rows, the Application object provides access to practically every element of Excel and Excel
workbooks.
With so many properties, it is impossible to know every available property, and it’s not nec­
essary to do so. There is a short list of about 10 properties that are the most common prop­
erties and should be learned to fully work with Excel using VBA. The other properties can be
learned as you need them. The important thing is to know that they are there for future
exploration.

Chapter 6

108