|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 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. |
#3
|
|||
|
|||
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 |