Page 154 - Excel Progamming Weekend Crash Course

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

Session 10 — Using Ranges and Selections 129

Figure 10-4 A worksheet cell can have a comment associated with it.
To delete a comment, call the ClearComments method. Combining this with the code
above, you can create a procedure that safely adds a comment to a range, as shown in
Listing 10-2. Note the use of the Not logical operator in this code. By reversing the logical
value returned by the expression r.Comment Is Nothing, the code in the If block exe-
cutes only if the range has a comment associated with it.

Listing 10-2 A procedure to add a comment to a cell

Public Sub AddComment(Comment As String, r As Range)
‘ Adds the specified comment to the cell
‘ specified by Range r. Deletes any existing
‘ comment from the cell.
If Not r.Comment Is Nothing Then
r.ClearComments
End If
r.AddComment Comment

End Sub

Never try to add a comment to a cell without first verifying that it does not
already have a comment.
Never