Part VI: Automating Excel
The following VBA statement clears a Range, an action that is equivalent to selecting the
Range and then choosing Home ➪ Editing ➪ Clear ➪ Clear All:
Range("A1:C12").Clear
The Clear method involves changing several properties of the Range object at once. It
includes setting the Value property to Empty (clearing its contents), the Bold property of
the Font object to False (clearing all formats), and the Comments property to Nothing
(deleting the cell’s comment). It’s doing a few other things as well.
If your code interacts with files on a disk, printers, or other aspects of your computer out-
side of Excel, you’ll probably use a method. Each Workbook object has a Name property
that’s read-only. You can’t change the Name property by setting it directly as follows:
Workbooks(1).Name = "xyz.xlsx"
That will fail. However, you can change a Workbook’s name by using the SaveAs method:
Workbooks(1).SaveAs "xyz.xlsx"
In addition to changing the name property, SaveAs changes a few other properties, and it
also writes the file to the hard drive.
In VBA code, methods look like properties because they’re connected to the object with a
“dot.” However, methods and properties are different concepts.
The Range object
The Range object is special. As you might imagine, it’s central to the Excel object model.
Workbooks and worksheets exist only to hold cells. But while the Worksheets collection
holds a bunch of Worksheet objects and the Shapes collection holds a bunch of Shape
objects, the Range object works differently.
A single cell is a Range object. A range of cells is also a Range object, but not a Ranges
collection object. It’s one of the few objects that breaks the plural naming convention of
collections.
Most collection objects have a default property of Item. That allows you to write the
following:
Workbooks(1)
instead of the following:
Workbooks.Item(1)
Generally, if an object has an Item property, that’s the default. For objects that aren’t col-
lections, if they have a Value property, that’s the default. For example, these two lines of
VBA are identical because Value is the default property of Checkbox objects:
If Sheet1.CheckBox1.Value = True Then
If Sheet1.CheckBox1 = True Then
