Performance better with For...Each or For i=1 to... loops?

[Benjamino5]

I'd read online that For...Each loops were faster in general than using a
counter. However, in the following code, AltMergeTable is running much faster
than MergeTable.

Did I get bad info about For...Each being faster? Or is it true in other
circumstances? I know I could (and will) do a lot of testing, but I'm curious
to know if there are any rules of thumb you can suggest for deciding whether
to use For...Each vs. using a counter.

My macro is running very slowly, and I'm trying to tackle it piece by piece,
so if you have any other ideas about how to speed up AltMergeTable even more,
please let me know!

Thanks,
Ben
______________________________________

Sub MergeTable(doc As Document, oTable As Table)
Dim oRow As Row
For Each oRow In oTable.Rows
oRow.Cells.Merge
If InStr(oRow.Range, "Ans:") = 0 Then
Call ParaMarkToTab(oRow.Cells(1))
End If
Next oRow
End Sub

Sub AltMergeTable(doc As Document, oTable As Table)
Dim i As Integer
For i = 1 To oTable.Rows.Count
oTable.Rows(i).Cells.Merge
If InStr(oTable.Rows(i).Range, "Ans:") = 0 Then
Call ParaMarkToTab(oTable.Rows(i).Cells(1))
End If
i = i + 1
Next i
End Sub

 

[Karl E. Peterson]

I'd read online that For...Each loops were faster in general than using a
counter. However, in the following code, AltMergeTable is running much faster
than MergeTable.

Sub AltMergeTable(doc As Document, oTable As Table)
Dim i As Integer
For i = 1 To oTable.Rows.Count
oTable.Rows(i).Cells.Merge
If InStr(oTable.Rows(i).Range, "Ans:") = 0 Then
Call ParaMarkToTab(oTable.Rows(i).Cells(1))
End If
i = i + 1
Next i
End Sub

Uhhhh... Cardinal Rule: Never mess with a loop index!

Try this:

Public Sub test()
Dim i As Long
For i = 1 To 10
Debug.Print i
i = i + 1
Next i
End Sub

I don't know anything at all about your specific problem domain, but I know a thing
or two about MSBasic. Methinks you cut your time because you didn't do (nearly)
half your work. Do you see why?

 

[Benjamino5]

Karl,

I'm afraid you're being too cryptic. I don't see how I did "half the work"
in the second sub--I'm sure I'm missing something, but it looks like both
subs do the same work.

Thanks,
Ben

 

[Karl E. Peterson]

I'm afraid you're being too cryptic. I don't see how I did "half the work"
in the second sub--I'm sure I'm missing something, but it looks like both
subs do the same work.

If you'd *tried* the test I gave you, you'd have seen:

1
3
5
7
9

Can it be any clearer than that? The For-Next loop is designed to automatically
increment the counter variable. You're also incrementing it. Baaaaaaad idea...

*IF* you ever had a need to skip every other (or any other increment) iteration, you
can use the Step keyword to indicate that. IOW, these two loops are identical:

Public Sub test()
Dim i As Long
For i = 1 To 10
Debug.Print i
i = i + 1
Next i
End Sub

Public Sub test()
Dim i As Long
For i = 1 To 10 Step 2
Debug.Print i
Next i
End Sub

 

[Benjamino5]

Ah, well thank you for explaining. I see the issue now.

 

[Julian]

FWIW, if the collection is large then For Each is probably faster... if you
use a counter to get e.g. a particular word in a range, as far as I can tell
Word always starts at 1 and counts forward. For Each I think does an implicit
".next" and so carries on from where it left off...

But it's not usually a problem until things get as big as my documents <G>

Julian

 

[David Sisson]

Like Karl said, without knowing what you're trying to accomplish, it's
hard to make recommendations. To answer specifically to your question,
Julian's answer is right.

Here's a few ideas.

Be sure to turn off screen updating.
Application.ScreenUpdating = False
'code here
Application.ScreenUpdating = True

1. Could you create a new document and send the contents of the row to
it without merging the cell?

2. Could you use Find instead of Instr to locate the text without
merging the cell?

3. Could you use Replace instead of calling the ParaToTab sub.
oRow = Replace(oRow, Chr(9), Chr(13))

 

[Karl E. Peterson]

Like Karl said, without knowing what you're trying to accomplish, it's
hard to make recommendations. To answer specifically to your question,
Julian's answer is right.

Oh, I dunno about that! You want a *really* fast For-Next loop, just skip
every-other iteration. <vbg>