Referring to ranges

Referencing a range relative to another range

Typically, the Range object is a property of a worksheet. It is also possible to have Range be the property of another range. In this case, the Range property is relative to the original range, which makes for unintuitive code. Consider this example:


This code actually selects cell D7. Think about cell C3, which is located two rows below and two columns to the right of cell A1. The preceding line of code starts at cell B5. If we assume that B5 is in the A1 position, VBA finds the cell that would be in the C3 position relative to B5. In other words, VBA finds the cell that is two rows below and two columns to the right of B5, which is D7.

Again, I consider this coding style to be very unintuitive. This line of code mentions two addresses, and the actual cell selected is neither of these addresses! It seems misleading when you’re trying to read this code.

You might consider using this syntax to refer to a cell relative to the active cell. For example, the following line of code activates the cell three rows down and four columns to the right of the currently active cell:


I mention this syntax only because the macro recorder uses it. Recall that when you recorded a macro in Chapter 1, “Unleashing the power of Excel with VBA,” with relative references on, the following line was recorded:

ActiveCell.Offset(0, 4).Range("A1").Select

This line found the cell four columns to the right of the active cell, and from there, it selected the cell that would correspond to A1. This is not the easiest way to write code, but it is the way the macro recorder does it.

Although a worksheet is usually the object of the Range property, occasionally, such as during recording, a range may be the property of a range.