Loop through fields gets progressively slower

[Peter]

Hello,

I have been trying to speed up a macro I am using to loop through fields. It
was taking over 3 hours to run (this on a dual-core E6600 with 2GB ram) so I
thought I'd troubleshoot and figure out where the bottlenecks are. I've
truncated all the code and left only the loop through the fields. Here is the
code:

Sub TraceMatXref()
Dim reqRange As Range, aField As Field, sw As StopWatch

If ActiveDocument.Bookmarks.Exists("myRange") Then
Set reqRange = ActiveDocument.Bookmarks("myRange").Range
Set sw = New StopWatch
sw.StartTimer
For Each aField In reqRange.Fields
Next aField
Debug.Print "Elapsed time:= " & sw.EndTimer
End If
End Sub

Stopwatch is a routine I got from the "VBA Developer's Handbook" by Getz &
Gilbert.
Anyway, there are only 375 fields in the range and yet it takes almost three
hours to run through this code! If I move the timer inside the loop, the
stopwatch function reports 0 time for each loop but the time between
iterations slows to a crawl as each loop is executed.

Anybody got any ideas what is going on here?

Thanks,
Peter

 

[Jay Freedman]

When a loop takes longer and longer as it proceeds, that's a sign that
"under the hood" the collection is counting up from the first item to the
current one each time. This is a bad -- but unfortunately common -- way of
implementing a collection accessor.

A workaround is to use the .Next method to step through the collection,
repeating that in a For loop calibrated to cover the required number of
iterations:

Sub demo()
Dim reqRange As Range, aField As Field, sw As StopWatch
Dim idx As Long, lastField As Long

If ActiveDocument.Bookmarks.Exists("myRange") Then
Set reqRange = ActiveDocument.Bookmarks("myRange").Range
lastField = reqRange.Fields.Count
If lastField > 0 Then
Set sw = New StopWatch
sw.StartTimer
Set aField = reqRange.Fields(1)
For idx = 2 To lastField
' work on aField
Set aField = aField.Next
Next idx
Debug.Print "Elapsed time:= " & sw.EndTimer
End If
End If
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Peter]

Thanks for the response. I figured out what the problem is with your help.
The range I specified is column 1 of a table and there are other fields in
cols 2 & 3. Apparently you are right in that each iteration has to start from
the beginning but only because of the way I defined the range. By redefining
the range so it includes the entire table, execution time went from 3 hours
down to 19 seconds!

Thanks again!
Peter

 

[Jay Freedman]

Ah, that would explain it. Thanks for the feedback.

Jay