Page 148 - Excel Progamming Weekend Crash Course

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

Session 10 — Using Ranges and Selections 123

Please refer to Sessions 12 and 13 for more information on using Excel for-
mulas in your code.
Cross-Ref

Many of the actions you can take with Range objects are covered in upcoming sessions.

Relative Ranges
The ranges you have seen so far are defined with respect to a worksheet and are always
identified in terms of the row numbers and column letters in that worksheet. Excel and VBA
also allow you to define relative ranges — a range that is not defined in terms of its location
in the worksheet, but in terms of its position relative to another range. This can be very
useful for writing code that acts on different worksheet locations depending on the circum-
stances. Look at this code:

Dim range1 As Range
Set range1 = ActiveSheet.Range(“B3”)
Now, the range r1 refers to cell B3 in the worksheet. Another way to look at it is that it
refers to the cell that is one column to the right, and two rows down, from the upper left
cell of the worksheet. This is illustrated in Figure 10-1.

One column to the right

A B
1 Two rows down
2

3
4

5
6
7

8

Figure 10-1 A range location can be thought of in terms of its position relative to
the top left cell in the worksheet.
Instead of using the Range property of a Worksheet object, suppose you use the Range
property of an existing Range object. For example:
Set range2 = range1.Range(“B3”)