Written by Allen Wyatt (last updated December 18, 2023)
This tip applies to Excel Excel in Microsoft 365
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.
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!
Adding comments or notes to the cells in your worksheets can help to document different aspects of that worksheet. Adding ...
Discover MoreExcel allows you to not only put information into cells, but into comments attached to those cells. Here's how to copy ...
Discover MoreExcel allows you to place pictures inside of comments. If you do this, you may have experienced a situation where the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments