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

Peterson · #1 05-14-2021, 11:14 PM

I need to selectively change highlight colors applied to names in comments. The macro I cobbled together takes about 5-1/2 minutes to run through ~430 comments. Does the following code have glaring inefficiencies, or am I just being impatient?

(I created a second macro to create a test doc with an equivalent number of comments, and I thought I'd include it here to be helpful, but the highlight-change macro only seems to work on an actual document - the test docs don't work [the first comment is skipped, Word hangs.)

Code:

Sub FindReplace_COMMENTS_OneHighlightColorToAnother() ' 05/14/2021

' Finds highlighted text in comments and replaces
' the highlight with a different color

' This code omits user input elements, for brevity

    Dim strFindColor As String
    Dim strReplaceColor As String
    Dim 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)):
    strFindColor = 5
    strReplaceColor = 4
                    
    For Each myComment In ActiveDocument.Comments
        
        DoEvents ' For large docs, Word crashes without DoEvents
        
        With myComment.Range.Find
            .ClearFormatting
            .Text = "Andy:"
            .Highlight = True
            .Forward = True
            Do While .Execute
                If .Found = True Then
                    With .Parent
                        If .HighlightColorIndex = Val(strFindColor) Then
                            .HighlightColorIndex = Val(strReplaceColor)
                            .Text = "Andy:"
                        End If
                    End With
                End If
            Loop
        End With
    Next

Application.ScreenUpdating = True
    
End Sub

Guessed · #2 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 · #3 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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