Page 57 - Excel for Scientists and Engineers: Numerical Methods

34 EXCEL: NUMERICAL METHODS

To refer to the active cell or a selected range of cells, use the ActiveCell or
Selection keywords. The ActiveCell keyword is usually used when the user has
selected a single cell, whereas the Selection keyword is used when the user has
selected a range of cells. However, Selection can refer to a single cell or a
range.

A Reference to a Cell Other than the Active Cell
Sometime a macro will be designed to operate on values from specified rows
and columns in a worksheet, independent of where the cursor has been "parked"
by the user. To refer to a cell or range other than the selection, use either the
Range keyword or the Cells keyword. The syntax of the latter is
Cells( Rowlndex, Colurnnlndex) .
The following references both refer to cell B3:
Range("B3")

Cells(3,2)
The preceding are "absolute" references, since they always refer to, in this
example, cell B3. You can also use what could be called a "computed"
reference, in which the reference depends on the value of a variable. The Cells
keyword is conveniently used in this way. For example, the expression

Cells(x,2)
allows you to select any cell in column B, depending on the value assigned to the
variable x. The Range keyword can be used in a similar way by using the
concatenation operator, e.g.,
Range("B" & x)
It's usually good programming practice not to use the Select keyword unless
you actually need to select cells in a worksheet. For example, to copy a range of
cells from one worksheet to another, you could use the statements shown in
Figure 2-12, and in fact this is exactly the code you would generate using the
Recorder. But you can do the same thing much more efficiently, and without
switching from one worksheet to another, by using the code shown in Figure 2-
13.

Range("D1: D2O").Select
Selection.Copy
Sheets("Sheetl5").Select
Range("Al").Select
ActiveSheet.Paste
Figure 2-12. VBA code fragment by the Recorder.