Page 150 - Excel Progamming Weekend Crash Course

h540629 ch10.qxd 9/2/03 9:34 AM Page 125

Session 10 — Using Ranges and Selections 125

2. The “C4” part of the argument says “two columns to the right and three rows
down.” Starting from the original cell B3, this takes you to cell D6 as the upper
left corner of the new range.
3. The argument “C4:E5” specifies a range that is 3 columns wide and 2 rows high.
Because the new range starts at cell D6, this means it extends to column F and
row 7; therefore, the final range2 is D6:F7.
Another way to create a relative range is using the Range object’s Offset property. This
differs from the Range object’s Range property because you specify the new range as a
numerical offset from the original range (as opposed to a row/column designation). It also
differs in that the new range automatically has the same size as the original range. The
syntax is:
OriginalRange.Offset(RowOffset, ColumnOffset)

The RowOffset and ColumnOffset arguments are numerical values that specify rows
below, and columns to the right, of OriginalRange. Use negative argument values to
specify columns to the left or rows above the original range. Here’s an example:
Dim range1 As Range, range2 As Range
Set range1 = ActiveSheet.Range(“D4:E5”)
Set range2 = range1.Offset(-2, 3)
After this code executes, range2 refers to the range G2:H3. Here’s why:

1. The original range had its top left cell at D4.
2. The ColumnOffset argument to the Offset property specified “3 columns to the
right,” or column G.
3. The RowOffset argument specified “2 rows up,” or row 2; therefore, the new range
has its top left corner at cell G2.
4. The original range was 2 × 2 cells in size, so the new range will be the same size.
When would you want to use relative ranges? They can be useful when you want to write
code to perform some action on the worksheet, but do not know ahead of time exactly
where that will be. For example, suppose that you want to format a column of numbers with
alternate rows in boldface. You could write a program that starts at the active cell, which
the user would be instructed to place at the top of the column. This could be anywhere in
the worksheet. Then the program would do the following:
1. Get a range that references the active cell.
2. Make that cell boldface.
3. Use the Offset property to get a new range that is two cells below the original
range.
4. Return to step 2, and repeat until an empty cell is encountered.
The program shown in Listing 10-1 demonstrates this use of a relative range. To use this
program, do the following:
1. Place a column of data (numbers and /or text) in a worksheet.