Programming Excel with VBA and .NET

Add Comments

Comments are a way to annotate cells on a worksheet with descriptive text. To add a comment in Excel:

1. Right-click the cell.

2. Choose Insert Comment from the pop-up menu.

3. Type your comment in the Edit region.

Cells with comments have a comment indicator in their upper-right corner. When the cursor pauses over the cell, the comment pops up as shown in Figure 10-1.

Each comment is anchored to a specific cell, so you create comments in code using the Range object’s AddComment method. Once a worksheet contains comments, you can get at them through the Worksheet object’s Comments collection or through the Next and Previous methods of the Comment object. This is a little different from the way most collections work: there is no Add method for the Comments collection. The following code adds a comment to each cell on a worksheet that contains a non-numeric value:

Figure 10-1. Use comments to annotate cells

Sub AddAuditComments(  )
    Dim cel As Range, cmt As Comment
    For Each cel In ActiveSheet.UsedRange
        If Not IsNumeric(cel.Value) Then
            cel.AddComment.Text "Audit:" & vbLf & "Should be a number?"
        End If
    Next
End Sub

You remove comments using the Delete method. For example, the following code removes the audit comments inserted by the preceding code:

Sub RemoveAuditComments( ) Dim cmt As Comment For Each cmt In ActiveSheet.Comments If InStr(1, cmt.Text, "Audit:") Then cmt.Delete Next End ...