How to count resolved comments only via VBA in Excel

Question

By using the Comments property of a worksheet it is possible to retrieve the number of comments in said worksheet by accessing its Count property in turn: Worksheets(1).Comments.Count.

The Comment object is a member of the Comments collection containing the following properties: Application, Author, Creator, Parent, Shape and Visible.

Given this information I was only able to retrieve the number of comments per worksheet but would also like to see the ammount and/or ratio of resolved to open comments. Am I missing some hack to utilize the provided properties (maby like Visible) to somehow get information of wheter the comment was marked as resolved?

It is not possible to delete the resolved comments since they are used as some sort of documentation of the decisions and changes.

Answer 1

• Only threaded comment has the resolved status. Normal comment (Insert by Review > Notes > New Note) doesn't have the status.

In M365, the terms comments and notes can be a bit confusing.

• The Comments group actually works with threaded comments object.
• The Notes drop-down menu works with comments object.

Microsoft documentation:

CommentThreaded object (Excel)

Sub CountCmt()
    Dim ct As CommentThreaded, iCnt As Long, iTotal As Long
    iTotal = ActiveSheet.CommentsThreaded.Count
    If iTotal = 0 Then
        MsgBox "No comments"
    Else
        For Each ct In ActiveSheet.CommentsThreaded
            If ct.Resolved Then iCnt = iCnt + 1
        Next
        MsgBox "Resolved comments: " & iCnt & vbCr & _
            "Total comments: " & iTotal
    End If
End Sub