Page 153 - Excel Progamming Weekend Crash Course

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

128 Saturday Morning

Suppose that you know a table has its upper left corner in cell B2 (as in Figure 10-3),
but you do not know how many rows and columns the table contains. You can create a
Range object that references the entire table as follows (this code assumes that the work-
sheet containing the table is active):
1. Create a Range object that references cell B2.
2. Get the CurrentRegion property from the Range object created in step 1.
The following code illustrates these steps:

Dim TableRange As Range
Dim CellRange As Range
Set CellRange = ActiveSheet.Range(“B2”)
Set TableRange = CellRange.CurrentRegion
Note that the above code could be shortened to the following, which accomplishes the
same thing:
Dim TableRange As Range
Set TableRange = ActiveSheet.Range(“B2”).CurrentRegion
After this code executes, the variable TableRange references the entire table (cells
B2:F5) and can be used to perform various actions such as changing the format.

Working with Comments
An Excel worksheet cell can have a comment attached to it. When a cell that contains a
comment is active, the comment displays in a balloon next to the cell, as shown in Figure
10-4. You can use comments for many purposes, such as documenting what data a cell holds
or providing reminders to the user.
To add a comment to a cell AddComment method of the Range object:

SomeRange.AddComment “Comment text”
Because of the way that Excel and VBA handle comments, you cannot simply modify a
comment by editing it or by adding a new comment to the cell. If you call the AddComment
method for a cell that already contains a comment, an error is generated; therefore, you
must see if the cell already contains a comment and, if so, remove it. Only then can you
safely add a comment to the cell.
To determine if a cell has a comment, compare its Comment property with the special
value Nothing. This is illustrated in the following code snippet:
If SomeRange.Comment Is Nothing Then
‘ Cell does not have a comment.
Else
‘ Cell has a comment.
End If