Overview
A custom worksheet function that returns a TRUE/FALSE array indicating which cells in a range have legacy comments (Notes) attached. Spills as a dynamic array matching the shape of the input range.
How It Works
- Comment_Range: The range of cells to check for legacy comments.
- Iterates through every cell and tests
Not .Comment Is Nothing. - Returns a 2D array that spills to match the input range dimensions.
Usage
=fn_Range_Has_Notes(A1:D20)
Returns a spilled array of TRUE/FALSE values. Combine with SUMPRODUCT to count commented cells.
The VBA Code
Function fn_Range_Has_Notes(Comment_Range As Range) As Variant
Application.Volatile
Dim Arr() As Variant
ReDim Arr(1 To Comment_Range.Rows.Count, 1 To Comment_Range.Columns.Count)
Dim r As Long, c As Long
For r = 1 To Comment_Range.Rows.Count
For c = 1 To Comment_Range.Columns.Count
Arr(r, c) = Not Comment_Range(r, c).Comment Is Nothing
Next c
Next r
fn_Range_Has_Notes = Arr
End Function