Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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: 143
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
 



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 11:07 PM.


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