Chapter 39: Introducing Visual Basic for Applications
815
Most of these objects can contain other objects. For example, a Workbook object can contain the
following objects:
l (^) Charts (a collection of Chart sheet objects)
l Names (a collection of Name objects)
l (^) Styles (a collection of Style objects)
l Windows (a collection of Window objects in the workbook)
l (^) Worksheets (a collection of Worksheet objects)
Each of these objects, in turn, can contain other objects. A Worksheet object, for example, can
contain the following objects:
l ChartObjects (a collection of all ChartObject objects)
l (^) PageSetup (an object that stores printing information)
l PivotTables (a collection of all PivotTable objects)
A collection consists of all like objects. For example, the collection of all Workbook objects is
known as the Workbooks collection. You can refer to an individual object in a collection by using
an index number or a name. For example, if a workbook has three worksheets (named Sheet1,
Sheet2, and Sheet3), you can refer to the first object in the Worksheets collection in either of
these ways:
Worksheets(1)
Worksheets(“Sheet1”)
Properties
The objects that you work with have properties, which you can think of as attributes of the objects.
For example, a Range object has properties, such as Column, Row, Width, and Value. A Chart
object has properties, such as Legend, ChartTitle, and so on. ChartTitle is also an object,
with properties such as Font, Orientation, and Text. Excel has many objects, and each has
its own set of properties. You can write VBA code to do the following:
l (^) Examine an object’s current property setting and take some action based on it.
l Change an object’s property setting.
You refer to a property in your VBA code by placing a period (a dot) and the property name after
the object’s name. For example, the following VBA statement sets the Value property of a range
named frequency to 15. (That is, the statement causes the number 15 to appear in the range’s
cells.)
Range(“frequency”).Value = 15
Some properties are read-only, which means that you can examine the property, but you can’t
change the property. For a single-cell Range object, the Row and Column properties are read-only