Page 147 - Excel Progamming Weekend Crash Course

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

122 Saturday Morning

In this syntax, wsRef is a reference to the worksheet, and ref identifies the cells that
the range will include, as follows (note that range references are given as strings in quotes):
For a single cell range, use the column letter and row number. For example, “A8” or
“Z29.”
For a multicell range, use the upper left cell and the lower right cell separated by a
colon. For example, “A8:B12.”

An alternative way to reference a multicell range is to identify the top
left and bottom right cells separately, separated by a comma. Thus,
Tip Range(“A8”, “B12”) is equivalent to Range(“A8:B12”). Also remember
that your program can define as many different Range objects as needed.
The following code references the single cell A8 in the currently active worksheet:

ActiveSheet.Range(“A8”)
You could use this reference to perform various actions, such as placing the numeric
value 123 or the text “Hello” in the cell:

ActiveSheet.Range(“A8”) = 123
ActiveSheet.Range(“A8”) = “Hello”

You can also create a variable that refers to a range:
Dim MyRange As Range
Set MyRange = ActiveSheet.Range(“A8”)
Now, MyRange refers to cell A8 and can be used to manipulate it.

If you set a range equal to a value (numeric or text), and the range refers to
more than one cell, the value is placed in all cells of the range. Existing data
Tip in the cells is overwritten without warning.

Setting a range to a value is the same as using the Range object’s Value
property. Thus, this line of code:
Note
ActiveSheet.Range(“A8”) = 123
has exactly the same effect as this one:
ActiveSheet.Range(“A8”).Value = 123

Manipulating Ranges
After you have a reference to a range, what can you do with it? You saw above how to insert
data into a range—simply assign the data to the range. You can place numeric data, text data,
or a formula in a cell this way. When inserting a formula, use a string containing the formula,
being sure to include the leading equal sign that tells Excel that the entry is a formula and not
just text. This code places a formula in cell B12 that adds the values in cells B10 and B11:
ActiveSheet.Range(“B12”) = “=B10+B11”