How to use Excel class Comment in VBA

Add Comment

Adding a comment is done using the AddComment method of the Range class. So first you will have to Set the Range - which may only be a single cell. Using the With you don't have to explicitly define a variable for the Comment so you can immediately set the properties such as the Text and possibly make it not visible.

Dim rng As Range: Set rng =
With rng.AddComment
    .Visible = False
    .Text "reviewed on " & Date
End With

Showing or hiding the Comment and Indicator

The current visibility of comments is determined using the Application.DisplayCommentIndicator property.

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

The other two values are xlCommentAndIndicator, which has the Comment visible, and xlNoIndicator.

Using colored comments

To accentuate differences between types of comments you can change the color. The color is set via the Shape.Fill property of the Comment. You can look up the RGB values here.

Dim cmm As Comment
cmm.Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)

Alternatively, you may use Color Scheme colors. The macro DisplayColourScheme can be used to see what value is used for each color in your color scheme.

cmm.Shape.Fill.ForeColor.SchemeColor = 19

Set Comment

You can Set a Comment variable by calling a method that returns a variable of that type. Below shows the most common use to select a comment, using the Comment method from a single cell range.

Dim rng As Range: Set rng =
Dim cmmComment As Comment
Set cmmComment = rng.Comment

The following methods can be also used to set a Comment variable. Click the link to visit the page that describes the method: Comment.Next, Comment.Previous, Worksheet.Comments.Item, Range.AddComment.

For Each in collection Comment

The code below shows how you can loop through a Comments collection which is available from the Worksheet class.

Dim ws As Worksheet 
Dim cmm As Comment 
For Each cmm In ws.Comments 

Next cmm

Test if a range has comments

The following code determines if a given range has comments. The Intersect method is needed in case your range contains only a single cell. because the SpecialCells method, as many other methods in Excel, assumes you want the whole worksheet if your specified range only contains a single cell.

Dim booRangeHasComments As Boolean
Dim rng As Range: Set rng =
On Error Resume Next
booRangeHasComments = (Intersect(rng, rng.SpecialCells(xlCellTypeComments)).Cells.Count > 0)

Delete Comment

Delete the Comment

Dim cmm As Comment 
cmm.Delete

Next Comment

Below shows the use of the Next method to move from some 'current' comment to the next (in the worksheet where the current range is located in):

Dim cmmCurrentComment As Comment
Dim cmmNextComment As Comment
Set cmmNextComment = cmmCurrentComment.Next()

Previous Comment

Returns a Comment object that represents the previous comment.

Dim cmmCurrentComment As Comment
Dim cmmNextComment As Comment
Set cmmNextComment = cmmCurrentComment.Next()

Retrieve or change the comment's text

Get the comment's text

To het the text of a comment you will use the Text method on a Comment.

Dim cmmCurrentComment As Comment
Dim cmmPreviousComment As Comment
Set cmmPreviousComment = cmmCurrentComment.Previous()

The above code does assume anything about how you got the Comment. If you want the comment of a Range, you can use shorter code by directly referring to the range and not first declaring a Comment variable:

Dim str As String
Dim rng As Range: Set rng =
str = rng.Comment.Text

Add Text

Usually you will set the text of the Comment during the creation of the comment using the AddComment method. But maybe you want to add extra text, for example a change date with each change you make. 

Dim rng As Range: Set rng = 
Dim cmm As Comment: Set cmm = rng.Comment
Dim strText As String: strText = Date
cmm.Text Text:=strText, Overwrite:=False

The above inserts text before any text already in the comment. If you want the insertion at the end, you will have to

1. get the current text of the comment (as above),
2. determine it's length (intLen),
3. Include the extra argument Start:=intLen in the method call.

Arguments:

• Text: The text to be added.
• Start: The character number where the added text will be placed.If this argument is omitted any existing text in the comment is deleted.
• Overwrite: True to overwrite the existing text.The default value is False (text is inserted).

Item Comment

Dim ws As Worksheet: Set ws =
Dim cmm As Comment
Set cmm = ws.Comments()

Arguments:

• Index: The index number for the object.

For more info see

Microsoft Office Object reference on Comment