MS WORD search macro slows way down as it runs

Chopper · #1 12-20-2020, 09:01 AM

I have had a fair amount of experience using VBA with Excel but recently I had to find a way to search large (several thousand lines) MS Word documents to pull out certain lines of text represented needed data - this was essentially my baptism in Word/VBA. Developing the search/identify process was relatively straightforward; it turned out that each line in the document could be treated as a paragraph (after a little judicious "pre-processing"), so I simply cycled through one paragraph at a time, checking for the desired text and outputting it when it was found - then on to the next line/paragraph. But then I ran into problems - cycling through the Word document, the macro ran slower and slower as it progressed until within a few minutes it ... just ... nearly ... stopped.

Skipping all the gnashing of teeth and rending of garments, what I eventually discovered is this:

Background - macro cycles through the document, one paragraph/line at a time, and when a desired piece of text is found the macro "if...thens" to code that handles the outputting/saving of the said text, then returns to the main program body to continue the cycling and searching process. A simple counter is used to keep track of where we are in the document, i.e., whenever a paragraph/line of text is processed, the counter is incremented; termination is achieved using a conditional after each cycle.

The Rub - apparently, in order to return to the correct position (as indicated by the above-mentioned counter) after jumping to the outputting/saving, VBA has to count from the beginning of the document after each output/save event!! Even though the counter tells it the location to return to, it has to count from the beginning to find that location. So ... as we progress farther and farther into the document, it takes longer and longer to resume the searching process. A document of a few dozen lines, no problem. Document of several thousand lines, BIG problem.

The fix was fairly simple after I finally figured out what was going on - I simply deleted each paragraph after it was examined. This means that the macro is always returning to paragraph #1 - no counting necessary. Of course this is analogous to "destructive testing" in that the original document ceases to exist after the macro runs, so keeping a master backup copy is essential.

Wondering if anyone else has encountered this? Have I misinterpreted what is going on? If so, I would be very interested in hearing about it. I have not posted my code since the problem doesn't appear (at least to me) to be code-dependent; it just seems like this is the way VBA behaves at a micro level. BTW, I did try defining the output/save code as a function and calling it that way, but the problem remained.

Any elucidative comments welcomed, and if this is an inappropriate query/comment I apologize in advance.

gmaxey · #2 12-20-2020, 09:44 AM

Chopper,

I believe this was way back in Word 2010 (which may have been with the release of VBA 7) that the For ... Loops in all of my add-ins started to clog as you described.
Read - Microsoft FU'ed Word VBA and never fixed it.

If you are looking for text, I don't really understand why you are looping in the first place. Perhaps if you show and describe what you are doing, one of us may have a suggestion for improvement.

Chopper · #3 12-20-2020, 10:14 AM

Greg:
Thanks for your reply. I'm not sure what you mean by "why ... looping" - perhaps the way I stated it is confusing. My macro simply moves through the Word doc one line/paragraph at a time (using Set pgphRange = ActiveDocument.Paragraphs(variable).Range), checks each line (contained in pgphRange) for certain text, processes (formats, outputs, saves) text if it meets certain requirements (or does nothing if the text does not meet those requirements), and goes to the next line/paragraph. [Upon reflection, I guess that does not really constitute "cycling" or "looping" - I apologize for being unclear].

Originally, I was incrementing "variable" (in the term above) to access the next line/paragraph, but that was what got me into trouble. Now I just replace "variable" with "1", and delete each line/paragraph as I finish with it. The whole thing is encased in a "Do ... Until no more lines/paragraphs" loop.

I can't post the code since the data is proprietary, and part of it would be exposed by the code.

Hope this clears things up.

eduzs · #4 12-20-2020, 11:03 AM

A find / replace can be a much more efficient way to replace text.

Chopper · #5 12-20-2020, 01:21 PM

edusz - that is true ... if I was replacing text ... which I'm not ... I'm pulling text out, re-formatting it, and putting it somewhere else.

eduzs · #6 12-20-2020, 01:43 PM

Did you tried with for/next?

Code:

For x = ActiveDocument.Paragraphs.Count To 1 Step -1
    ...
Next x

Some cases this worked for me.

gmaxey · #7 12-20-2020, 02:06 PM

Chopper,

You don't have to replace text if you find it. You just want to do something with that text when found. I think eduzs point, and mine, to find the text wherever it is in the thousands of paragraphs and process it. For example, I created as document with 10,000 paragraphs of similar text then I went in and added "XXX" to about a dozen or so.

See these examples:

Code:

Sub Example1()
'Burtally slow. Will probably lock up Word.
Dim oPar As Paragraph
Dim lngIndex As Long
Dim lngCount As Long
  For lngIndex = 1 To ActiveDocument.Paragraphs.Count
    Set oPar = ActiveDocument.Paragraphs(lngIndex)
    If InStr(oPar.Range.Text, "XXX") > 0 Then
      lngCount = lngCount + 1
      DoEvents
      'Do something with found text
    End If
  Next
  MsgBox lngCount
lbl_Exit:
  Exit Sub
End Sub

Sub Example2()
'Faster but still slow
Dim oPar As Paragraph
Dim lngCount As Long
  For Each oPar In ActiveDocument.Paragraphs
    If InStr(oPar.Range.Text, "XXX") > 0 Then
      lngCount = lngCount + 1
      DoEvents
      'Do something with found text
    End If
  Next
  MsgBox lngCount
lbl_Exit:
  Exit Sub
End Sub

Sub Example3()
'Even faster
Dim oPar As Paragraph
Dim lngCount As Long
  Set oPar = ActiveDocument.Paragraphs(1)
  Do
    If InStr(oPar.Range.Text, "XXX") > 0 Then
      lngCount = lngCount + 1
      'Do something with found text
    End If
    Set oPar = oPar.Next
  Loop Until oPar.Range.End = ActiveDocument.Range.End
  MsgBox lngCount
End Sub

Sub Example4()
'Likely faster yet because we only look a the paragraphs containing the text to find.
Dim oRng As Range
Dim oPar As Paragraph
Dim lngCount As Long
  Set oRng = ActiveDocument.Range
  With oRng.Find
    .Text = "XXX"
    While .Execute
      lngCount = lngCount + 1
      'Do something with found text
      oRng.Collapse wdCollapseEnd
    Wend
  End With
  MsgBox lngCount
End Sub

macropod · #8 12-20-2020, 06:28 PM

If all you want to do is to output content related to a particular string to another document, see:
https://www.msofficeforums.com/word-...n-strings.html
https://www.msofficeforums.com/word-...-document.html
https://www.msofficeforums.com/word-...paragraph.html

Chopper · #9 12-21-2020, 03:49 PM

Epigram - Finally solved the speed problem by using the following approach:

dim p as Paragraph
-
-
for each p in p.activedocument.paragraphs
pgphRange = p.Range.test
-
-
yadda, yadda processing contents of pgphRange ...
-
-
Next p

Runs like lightning (8000+ lines/paragraphs in under 10 seconds!), no need to delete processed lines/paragraphs, less code, whiz-bang!

I'm such a dumb@ss for not figuring this out 3 days ago.

Thanks all for helping to point me in the right direction.

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
Bold Button Runs Macro	DRD992	Word VBA	1	11-22-2017 02:23 PM
Word slows down as file size increases	johnmill@dymeroaks.net	Word	3	02-05-2016 05:49 PM
Word periodically slows / freezes when typing or printing	KathyReid	Word	3	09-26-2015 10:49 AM
Help:word editing slows down when copying a block diagram	seeker_123	Word	1	03-02-2015 09:26 AM
Word slows to a crawl.	traumatiziert	Word	1	04-18-2012 12:42 AM