Recognizing Notes and Comments in a Macro

Greg has a macro that is used to process information in a worksheet's comments. (These are now called notes by the smart folks in Redmond.) Since a workbook can now contain both comments and notes, Greg wonders how his macro can differentiate between the two and process both notes and comments.

Greg is correct—comments, in Microsoft's parlance, are something entirely different from what they used to be. What used to be called comments are now called notes, and the new comments are handled entirely differently than they used to be handled. The biggest difference is that the new comments allow you to have ongoing discussions with other people who may be working with your workbooks. For this reason, comments are often now called threaded comments, in an effort to remove some of the confusion introduced around this change.

When it comes to VBA, notes are contained in the Comments collection. Remember—notes used to be called comments, so it is understandable that VBA would still have the Comments collection. Threaded comments are contained in the CommentsThreaded collection.

To process notes in a macro, simply step through the Comments collection, similar to this:

Sub ProcessNotes1()
    Dim n As Comment

    For Each n In ActiveSheet.Comments
        ' Do your processing of each note here
    Next n
End Sub

This structure for a simple macro should not be foreign to Greg, as he indicated that he already has a macro that processes notes. Processing (at the point indicated by the comment in the macro) is done by accessing the members (properties and methods) of the note's object (n).

Of course, you could orient your processing a bit differently, since notes are attached to individual cells. In this orientation, you could step through a range of cells, see if the cell has an attached note, and then take whatever action is deemed necessary if the note exists:

Sub ProcessNotes2()
    Dim c As Range
    Dim n As Comment

    For Each c In Selection
        Set n = c.Comment
        If Not n Is Nothing Then
            ' Do your processing of each note here
            Set n = Nothing
        End If
    Next c
End Sub

If you want to work with threaded comments, all you need to do is to utilize the CommentsThreaded collection:

Sub ProcessComments1()
    Dim com As CommentThreaded

    For Each com In ActiveSheet.CommentsThreaded
        ' Do your processing of each comment here
    Next com
End Sub

The biggest potential gotcha with this coding is the object name vs. the collection name. Note that the plural part of the collection is on "Comment", as "CommentsThreaded." A singular comment, however, uses the CommentThreaded object type. As with notes, your processing is done by accessing the members (properties and methods) of the comment's object.

Comments, like notes, are associated with individual cells. Thus, you could also access them by stepping through a range of cells and seeing if they exist:

Sub ProcessComments2()
    Dim c As Range
    Dim com As CommentThreaded

    For Each c In Selection
        Set com = c.CommentThreaded
        If Not com Is Nothing Then
            ' Do your processing of each comment here
            Set com = Nothing
        End If
    Next c
End Sub

As a practical example of how you can process threaded comments, let's say that you want to step through each threaded comment in a worksheet and check to see if there are any replies to the comments. If there is a cell that has a comment, but there is no reply to it, then your macro adds a reply that indicates the comment still needs to be checked. If there was a reply to the comment, then nothing happens with it.

Sub ProcessComments3()
    Dim com As CommentThreaded
    Dim cr As CommentsThreaded
    Dim sTemp As String

    For Each com In ActiveSheet.CommentsThreaded
        Set cr = com.Replies
        If cr.Count = 0 Then
            ' No replies, so set a new reply
            sTemp = com.Author.Name & ", please check on this"
            com.AddReply sTemp
        End If
        Set cr = Nothing
    Next com
End Sub

Notice that the .Replies method returns a CommentsThreaded collection, which is assigned in this macro to the cr variable. The .Count property for the collection can then be checked to see if there are any replies to the comment. If there are none, then the .Author.Name property is used in the reply that is added to the original comment.

There is one more method that should be addressed, and that is the method used to add a threaded comment to a cell. This method is .AddCommentThreaded, and it is actually a member of a range. Thus, you could add a threaded comment to a cell in the following way:

Sub ProcessComments4()
    Dim c As Range
    Dim com As CommentThreaded
    Dim sTemp As String

    For Each c In Selection
        sTemp = Trim(c.Text)
        sTemp = LCase(sTemp)
        If sTemp = "trigger" Then
            Set com = c.CommentThreaded
            If com Is Nothing Then
                c.AddCommentThreaded "Check this cell"
                Set com = Nothing
            End If
        End If
    Next c
End Sub

This macro steps through a range of selected cells and checks to see if the cell contains the text "trigger". If it does, then a comment is added to the cell indicating that the cell should be checked.

If you would like more ideas on how to process threaded comments in a macro, you will appreciate the information available at this website:

https://www.contextures.com/excelthreadedcommentmacros.html

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8307) applies to Microsoft Excel 2021.