Page 192 - Microsoft Office Excel 2003 Programming Inside Out

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

Referring to Ranges

The versatility of ranges is manifested in the number of ways you can refer to a range.
Whether directly by cell address, as an offset from another cell, by name, or by using the cur-
rent selection, referring to a range has many useful options. The Range property allows sev­
eral methods of referring to a range. The syntax for the Range property is

expression.Range(Cell1, Cell2)
expression is a required element that should return either an Application, a Range, or a
Worksheet object. If expression is omitted, ActiveSheet is implied.
Cell1 is a required variant that supplies the A1-reference to a cell or range of cells. If you use
the Cell1 parameter to refer to a range of cells, the variant can include the range operator (a
Chapter 8
colon), the intersection operator (a space) or the union operator (a comma). Dollar signs ($)
can be included, but they are ignored. A locally defined name can also be supplied.
Cell2 is an optional variant that, when paired with Cell1, specifies the cell in the lower-right
corner of the range.

As the property description implies, there are many ways you can specify which cells to include.
You can use absolute references by using the ActiveSheet as the base object (expression), or you
can use relative references by using the ActiveCell or other range object as the base object. You
can specify a single cell, a group of continuous cells, a group of intermittent cells, or entire
rows or columns.

Referencing Ranges on the Active Worksheet

Referring to a range on the active worksheet is the most common method, and the majority
of range references in VBA are geared toward using the active worksheet. You can refer to a
range on the ActiveSheet by simply using the Range property. For example, to refer to cell D6
on the active worksheet, you could use the following code: Range("D6").Select.

Inside Out

Staying Away from Select
Although the tendency is to always select a range first, it’s not necessary to do so and it will
put a performance hit on your procedure. (See “Copying Data Between Ranges and Arrays”
later in this chapter for more information.) If you have to use only a single formatting com­
mand, specify the range and the formatting all in one line, rather than selecting the range
first and then doing the formatting.
As an example, to highlight the entire list of 6:00 P.M . entries in the Y2001ByMonth.xls
workbook and make the font color blue, you would use the following command:
Range("M6:M36").Font.ColorIndex = 41

166