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

Combinations for Members in Meetings

Got a large group of people listed in a worksheet and you want to make sure that each person has met with every other ...

Discover More

Inserting a Text Box

Many people use text boxes to help organize and layout information on the page. Here's how you can add text boxes to your ...

Discover More

Printing Based on Cell Contents

Would you like to have a worksheet automatically printed when a particular cell contains a specified value? You can ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Keyboard Shortcut for Comments

Adding comments or notes to the cells in your worksheets can help to document different aspects of that worksheet. Adding ...

Discover More

Pasting a Comment into Your Worksheet

Excel allows you to not only put information into cells, but into comments attached to those cells. Here's how to copy ...

Discover More

Locking the Size of Pictures in Comments

Excel allows you to place pictures inside of comments. If you do this, you may have experienced a situation where the ...

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 six minus 4?

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.