Looping through comments is slow: inefficiently written code or par for the course?

Guessed · #1 05-16-2021, 01:43 AM

Do you really need to be specific with the find highlight color? Isn't it constrained enough being in the comments, having the text "Andy:" and being highlighted?. Also you shouldn't need to loop through each comment if you search in the storyrange.

Code:

Sub FindReplace2() ' 05/14/2021
  Dim iFindColor As Integer, iReplaceColor As Integer, myComment As Comment
  
  'Application.ScreenUpdating = False
  
  ' (These variables are ordinarily set via input box
  ' 7 = yellow; 5 = pink; 4 = Bright Green; 3 = Turquoise (Bright Blue)):
  iFindColor = 5
  iReplaceColor = 4
                  
  'DoEvents ' For large docs, Word crashes without DoEvents
  Options.DefaultHighlightColorIndex = iReplaceColor
  With ActiveDocument.StoryRanges(wdCommentsStory).Find
    .ClearFormatting
    .Text = "Andy:"
    .Highlight = True
    .Replacement.Highlight = True
    .Forward = True
    .Execute Replace:=wdReplaceAll
  End With

  'Application.ScreenUpdating = True
  MsgBox "Done"
End Sub

If you still need to include the hilite color test then the macro drags a whole lot slower. This chunk looks cleaner than what you had but I don't know that it will be noticeably faster.

Code:

  With ActiveDocument.StoryRanges(wdCommentsStory).Find
    .ClearFormatting
    .Text = "Andy:"
    .Highlight = True
    .Replacement.Highlight = True
    .Forward = True
    .Execute
    Do While .Found = True
      If .Parent.HighlightColorIndex = iFindColor Then .Parent.HighlightColorIndex = iReplaceColor
      .Execute
    Loop
  End With

Peterson · #2 05-17-2021, 08:55 PM

Thank you for taking a look at and reworking these, Andrew. I could've sworn there was a comments story, but when a quick online search didn't turn it up, I concluded I'd have to loop through each comment. Need to get better at using the object browser...

When searching for names, no, the highlight color isn't important. But I wanted to include the color-specific functionality so that the macro could be used for other purposes, e.g., we occasionally have large, multi-author documents in which highlight colors are used to denote various things, and it'd be useful to be able to selectively clear them.

Thanks again.

EDIT: Your version that tests color ran through my doc with ~430 comments in 15 seconds.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error 4605 when looping through files in folder and deleting comments	Peterson	Word VBA	2	04-19-2018 08:45 AM
Specific Macro Written	brent_bris	Excel Programming	0	02-20-2017 05:55 AM
Slow internet = Slow Word...	User12344321	Word	4	09-21-2015 12:54 PM
Need a formula written for me	Dbjenkin	Excel	1	05-13-2014 05:46 AM
Code Error - Hiding comments and revisions	silvrwoman	Word VBA	6	03-24-2012 10:15 PM