Recognizing Notes and Comments in a Macro

Written by Allen Wyatt (last updated December 18, 2023)
This tip applies to Excel Excel in Microsoft 365


3

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 Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Enlarging the Formula Bar

The Formula bar is used to display the formula that appears in a cell. You may want to modify how the Formula bar is ...

Discover More

Resetting Paragraph Formatting

Tired of the formatting used in a paragraph? One way to 'start over' is to make sure that the formatting is reset to its ...

Discover More

Changing the Program that Opens a File

If you have multiple versions of the same program on your system, Windows can become confused as to which version it ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Anchoring Comment Boxes in Desired Locations

Want your comment boxes to appear someplace other than the right side of a cell? You may be out of luck, and here's why.

Discover More

Changing the Background Color for a Comment

Comments are a great way to document your worksheets. Excel provides you the tools you need in order to format your ...

Discover More

Adjusting Comment Printouts

Need to print out comments, but in a way that you control what is included in the printout? Here's a way you can extract ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2023-12-18 09:06:44

Allen

DaveS, you are correct. Good catch.

-Allen


2023-12-18 05:28:55

DaveS

In Sub ProcessComments2 and Sub ProcessComments4 should

Dim com as Comment

be

Dim com as CommentThreaded

?


2023-12-16 10:44:40

J. Woolley

My Excel Toolbox includes the following dynamic array function that can be used in a cell formula or called by Greg's macro:
    ListComments([AllSheets], [Threaded], [SkipHeader])
This function returns one row for each comment with the following columns: Worksheet, Cell, Author, Comment (text). It works with threaded or unthreaded comments. In older versions of Excel you can use ListComments in a cell formula with the SpillArray function described in UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox/


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.