Page 193 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Referencing Ranges on an Inactive Worksheet
The number of worksheets in an Excel workbook is limited only by the amount of available
memory. Information can be spread across any number of worksheets, not just the active
worksheet. To reference a range on an inactive worksheet, you need to specify the worksheet
that contains the range.
Using the Range property on an inactive worksheet is no different from using it on the
ActiveSheet. When the worksheet object is not specified, the active worksheet in implied.
Going back to previous example of reference cell D6, on an inactive worksheet it would be
Chapter 8
referenced using code similar to the following:

Worksheets(2).Range("D6").Select.
It is possible to reference a range on an inactive worksheet without specifying the worksheet,
but only if the range is named. (See “Using Named Ranges” later in this chapter.) By using the
statement Range("Frequency").Select the named range Frequency is selected, regardless
of whether it is on the active worksheet or not.

Referencing Cells in a Range
Referencing an individual cell within a range works the same as referencing a cell within the
entire workbook. The cell in the top-left corner of the range would be addressed as “A1”. The
cell three rows down and four columns to the right would be cell “D3”. As an example, in the
workbook Y2001ByMonth.xls, you could assign the range D6:O36 to a range object. To ref­
erence the 9:00 A.M. entry on the first day (cell D6 in the worksheet), you would use cell A1
of the range object. Likewise, the 7:00 P.M. of the sixteenth day (cell N21 in the worksheet)
would be cell K16 of the range object.

Referencing Cells Using the Offset Property

If the information you need to use is located in a particular location away from a known cell,
you can use the Offset property to reference the cell. By specifying the number of rows and
columns from a set location, you can reference the cell. It’s similar to giving driving direc­
tions where you tell someone to start at a particular location. The person you’re giving direc­
tions to needs to get to the starting spot on their own, but once there, they can follow your
directions to reach the final destination.
The Offset property works by moving the number of rows and columns specified. Positive
numbers move down and to the right while negative numbers move up and to the left. A
zero maintains the current row or column. The Calculate_Table routine inside the Loan
Calculation.xls workbook, shown in Figure 8-1, uses the Offset property to set the formula
for the Present Value and Interest Paid columns.

167