Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2021, 11:14 PM
Peterson Peterson is offline Looping through comments is slow: inefficiently written code or par for the course? Windows 10 Looping through comments is slow: inefficiently written code or par for the course? Office 2019
Competent Performer
Looping through comments is slow: inefficiently written code or par for the course?
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default Looping through comments is slow: inefficiently written code or par for the course?

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
Reply With Quote
  #2  
Old 05-16-2021, 01:43 AM
Guessed's Avatar
Guessed Guessed is offline Looping through comments is slow: inefficiently written code or par for the course? Windows 10 Looping through comments is slow: inefficiently written code or par for the course? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Last edited by Guessed; 05-16-2021 at 05:30 PM.
Reply With Quote
  #3  
Old 05-17-2021, 08:55 PM
Peterson Peterson is offline Looping through comments is slow: inefficiently written code or par for the course? Windows 10 Looping through comments is slow: inefficiently written code or par for the course? Office 2019
Competent Performer
Looping through comments is slow: inefficiently written code or par for the course?
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping through comments is slow: inefficiently written code or par for the course? 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
Looping through comments is slow: inefficiently written code or par for the course? Slow internet = Slow Word... User12344321 Word 4 09-21-2015 12:54 PM
Looping through comments is slow: inefficiently written code or par for the course? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft