Part VI: Automating Excel
Fortunately, VBA provides some shortcuts. Since Application is at the top, you can omit
it, and VBA will know what you want. VBA also provides default properties for some objects.
All collection objects have a default property called Item that’s used to access one of the
collections. You can shorten your code as follows:Workbooks("MyBook.xlsx").Worksheets(1).Range("A1")When accessing an Item of a collection, you can ask for it by name or by number. For the
Workbooks collection, we passed in the name of the workbook that we wanted, and it
returned the Workbook object with that name. For the Worksheets collection, however,
we asked for the first Worksheet object in the collection regardless of its name.Properties
The objects 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:■ Examine an object’s current property setting and take some action based on it.■ (^) 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
You might have noticed that we used the dot operator to traverse the hierarchy in the
previous section, and we’re using it again to access properties. That’s not a coincidence.
Properties can contain a lot of different values, and they can also contain other objects.
When we used Application.Workbooks("MyBook.xlsx"), we were actually accessing
the Workbooks property of the Application object. That property returns a Workbooks
collection object.
Some properties are read-only, which means 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 properties: you can determine where a cell is located (in which row and column),
but you can’t change the cell’s location by changing these properties.
A Range object also has a Formula property, which is not read-only; that is, you can
insert a formula into a cell by changing its Formula property. The following statement
inserts a formula into cell A12 by changing the cell’s Formula property:
Range("A12").Formula = "=SUM(A1:A10)"