Datasheet

19
Chapter 1: What Is VBA?
objects. A Worksheet object can contain objects such as Range objects
and PivotTable objects. The term object model refers to the arrangement
of these objects. (Object model mavens can find out more in Chapter 4.)
✓ Objects of the same type form a collection. For example, the
Worksheets collection consists of all the worksheets in a particular
workbook. The Charts collection consists of all Chart objects in a work-
book. Collections are themselves objects.
✓ You refer to an object by specifying its position in the object hierar-
chy, using a dot (that is, a period) as a separator. For example, you can
refer to the workbook Book1.xlsx as
Application.Workbooks(“Book1.xlsx”)
This refers to the workbook Book1.xlsx in the Workbooks collection.
The Workbooks collection is contained in the Application object (that is,
Excel). Extending this to another level, you can refer to Sheet1 in Book1.
xlsx as
Application.Workbooks(“Book1.xlsx”). _
Worksheets(“Sheet1”)
As shown in the following example, you can take this to still another
level and refer to a specific cell (in this case, cell A1):
Application.Workbooks(“Book1.xlsx”).
Worksheets(“Sheet1”).Range(“A1”)
✓ If you omit specific references, Excel uses the active objects. If Book1.
xlsx is the active workbook, you can simplify the preceding reference as
follows:
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the refer-
ence even more:
Range(“A1”)
✓ Objects have properties. You can think of a property as a setting for
an object. For example, a Range object has such properties as Value
and Address. A Chart object has such properties as HasTitle and Type.
You can use VBA to determine object properties and also to change
properties.
✓ You refer to a property of an object by combining the object name
with the property name, separated by a dot. For example, you can refer
to the Value property in cell A1 on Sheet1 as follows:
Worksheets(“Sheet1”).Range(“A1”).Value
05_503690-ch01.indd 1905_503690-ch01.indd 19 4/12/10 11:22 PM4/12/10 11:22 PM