Overview
A custom worksheet function that extracts all threaded comments and their replies into a structured table with Cell, Index, Type, Author, Date, and Text columns. Perfect for audit trails and review documentation.
How It Works
- Comment_Thread_Range: The range of cells to scan for threaded comments.
- First pass counts all comments and replies to size the output array.
- Second pass fills a 6-column array: Cell address, Index, Type (Comment/Reply), Author name, Date, and Text.
- Spills as a dynamic array with a header row.
Usage
=fn_Extract_Comments_Thread(A1:Z100)
Returns a table with columns: Cell, Index, Type, User, Date, Text. Each comment and its replies appear as separate rows.
The VBA Code
Function fn_Extract_Comments_Thread(Comment_Thread_Range As Range) As Variant
Application.Volatile
' Returns a 2D array: [Cell, Index, Type, Author, Date, Text]
Dim Rng As Range, Lin_Arr As Long
For Each Rng In Comment_Thread_Range
If Not (Rng.CommentThreaded Is Nothing) Then
Lin_Arr = Lin_Arr + 1 + Rng.CommentThreaded.Replies.Count
End If
Next Rng
Dim Arr() As Variant: ReDim Arr(0 To Lin_Arr, 1 To 6)
Arr(0, 1) = "Cell": Arr(0, 2) = "Index": Arr(0, 3) = "Type"
Arr(0, 4) = "User": Arr(0, 5) = "Date": Arr(0, 6) = "Text"
Dim Cmt_Idx As Long, Rlp_Idx As Long
For Each Rng In Comment_Thread_Range
If Not (Rng.CommentThreaded Is Nothing) Then
Cmt_Idx = Cmt_Idx + 1: Rlp_Idx = Rlp_Idx + 1
Arr(Rlp_Idx, 1) = Rng.Address(False, False)
Arr(Rlp_Idx, 3) = "Comment"
Arr(Rlp_Idx, 4) = Rng.CommentThreaded.Author.Name
Arr(Rlp_Idx, 5) = Rng.CommentThreaded.Date
Arr(Rlp_Idx, 6) = Rng.CommentThreaded.Text
Dim Rlp As Object
For Each Rlp In Rng.CommentThreaded.Replies
Rlp_Idx = Rlp_Idx + 1
Arr(Rlp_Idx, 1) = Rng.Address(False, False)
Arr(Rlp_Idx, 3) = "Reply"
Arr(Rlp_Idx, 4) = Rlp.Author.Name
Arr(Rlp_Idx, 5) = Rlp.Date
Arr(Rlp_Idx, 6) = Rlp.Text
Next Rlp
End If
Next Rng
fn_Extract_Comments_Thread = Arr
End Function