I'm iterating through shapes of excel sheet. Some of shapes are comment shapes (sh.type == msoComment). I need to find cell attached to comment shape.

for each sh in activesheet.shapes
  if sh.type == msoComment then
    debug.print get_cell_from_shape(sh).address
  end if
next

I know that I can iterate through cells and check if there is a comment, but it's to slow. I'm working on huge Excel files with only few comments. I've tried sh.parent but it returns worksheet. I've tried sh.topleftcell, but its wrong cell (not the one I need).

CodePudding user response：

You may want to consider iterating through both the ActiveSheet.Comments and ActiveSheet.CommentsThreaded because what used to be called a Comment is now referred to as a Note in Excel 365.

• New Comment will add a CommentThreaded to the cell
• New Note will add a Comment to the cell.

Function GetCommentedCells() As Collection
    Dim Collection As New Collection
    Dim Comment As Comment
    For Each Comment In ActiveSheet.Comments
        Collection.Add Comment.Parent
    Next
    
    Dim CommentThreaded As CommentThreaded
    For Each CommentThreaded In ActiveSheet.CommentsThreaded
        Collection.Add CommentThreaded.Parent
    Next
    
    Set GetCommentedCells = Collection
End Function

CodePudding user response：

Never mind... I can iterate through comments directly

for each com in activesheet.comments
  debug.print com.parent.address
next

and now .parent works fine