Page 159 - Excel Progamming Weekend Crash Course

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

134 Saturday Morning

ActiveWorkbook.Names.Add Name:=”Total” RefersTo:=ActiveSheet.Name & “!A2”

Ranges that are created in this way are referred to in the same way as the temporary
named ranges discussed earlier. For example:

Range(“Total”).Value = 123
The following code has the same effect as the previous example:

Dim r As Range
Set r1 = Range(“Total”)
r1.Value = 123

The Selection Property

The Selection property is similar to a Range object in that it refers to a range of one or
more cells. Specifically, it refers to the cell(s) that have been selected by the user by click-
ing or dragging with the mouse, or using the keyboard. A given worksheet can have only
one selection at a time. This is different from ranges, where there is no limit.
Another difference is that while a Range object always refers to a range of one or more
cells, the Selection property may refer to something else, such as a chart in the work-
sheet. You can verify that the Selection property is pointing to a range of cells using the
TypeName function. When passed the Selection property as its argument, this function
returns the string “Range” if one or more cells is selected. If something else is selected, it
returns a different string. (If nothing is selected, the Selection property returns Nothing.)
Therefore, rather than assuming that Selection indicates a range, you would use code such
as the following:

If TypeName(Selection) = “Range” Then
‘ Selection is a range.
Else
‘ Selection is not a range.
End If

The Selection property returns a Range object that references the selected cells. After
you have this Range object, you use it to perform whatever actions are needed. The previous
code snippet would be modified to the following:

Dim r1 As Range
If TypeName(Selection) = “Range” Then
Set r1 = Selection
‘ Now use r1 to work with the selected cells.
End If
Why would you use the Selection object in place of a Range object? There is only one
situation where I have found this useful: when you want to let the user select the cells that
the program will act on. There are two approaches to letting the user specify a range. One is
to instruct the user to select a range of cells and then run your program. An example of this
technique can be found in Session 11. The other approach is to use a RefEdit control,
which is covered in Session 29.