For Each Except First

[George B]

I am processing each row of a table, with the following code

For Each oRow in table.Rows
Process this row
Next oRow

The first row is a list of column titles. What's the easiest way to process
only rows 2-end?

 

[Jay Freedman]

Here are two variations:

Sub sample1()
Dim oTbl As Table
Dim oRow As Row
Set oTbl = ActiveDocument.Tables(1)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
MsgBox oRow.Cells(1).Range.Text
End If
Next
End Sub

Sub sample2()
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = ActiveDocument.Tables(1)
' start processing at row 2
For rowNum = 2 To oTbl.Rows.Count
Set oRow = oTbl.Rows(rowNum)
MsgBox oRow.Cells(1).Range.Text
Next
End Sub

It would take some testing to determine which is faster, but I'd put my
money on sample1.

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

 

[Robert M. Franz (RMF)]

Hi Jay

Here are two variations:

Sub sample1()
Dim oTbl As Table
Dim oRow As Row
Set oTbl = ActiveDocument.Tables(1)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
MsgBox oRow.Cells(1).Range.Text
End If
Next
End Sub

Sub sample2()
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = ActiveDocument.Tables(1)
' start processing at row 2
For rowNum = 2 To oTbl.Rows.Count
Set oRow = oTbl.Rows(rowNum)
MsgBox oRow.Cells(1).Range.Text
Next
End Sub

It would take some testing to determine which is faster, but I'd put my
money on sample1.

Just curious: In the first example, you have to make one comparision for
each row. Is that a piece of cake at runtime, or would it only matter in
the tables we all want to avoid (like, 10'000 rows or something )?

Greetinx
Robert

 

[Jay Freedman]

Hi Jay



Just curious: In the first example, you have to make one comparision for
each row. Is that a piece of cake at runtime, or would it only matter in
the tables we all want to avoid (like, 10'000 rows or something )?

Greetinx
Robert

Hi Bob,

I don't know for sure. It depends on how MS implemented the .Index
property. If the runtime has to count from the top of the table each
time to get that number, it could get very slow for very large tables.
The comparison is trivial compared to that.

For the same table, though, I think the second example would be even
worse because the statement

Set oRow = oTbl.Rows(rowNum)

does involve counting from the top of the table, plus an object
assignment.

Feel free to run some tests and report the results.

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

 

[Robert M. Franz (RMF)]

Jay Freedman wrote:
[..]

I don't know for sure. It depends on how MS implemented the .Index
property. If the runtime has to count from the top of the table each
time to get that number, it could get very slow for very large tables.
The comparison is trivial compared to that.

Hmm, this is no array with fixed dimensions where one would presume the
same amount of time to fetch a[100] compared to a[1], right.

For the same table, though, I think the second example would be even
worse because the statement

Set oRow = oTbl.Rows(rowNum)

does involve counting from the top of the table, plus an object
assignment.

Feel free to run some tests and report the results.

I'd rather bow to your judgement.

Learnt something from that today already!

Greetinx
Robert

 

[Jay Freedman]

Jay Freedman wrote:
[..]

I don't know for sure. It depends on how MS implemented the .Index
property. If the runtime has to count from the top of the table each
time to get that number, it could get very slow for very large tables.
The comparison is trivial compared to that.

Hmm, this is no array with fixed dimensions where one would presume the
same amount of time to fetch a[100] compared to a[1], right.

For the same table, though, I think the second example would be even
worse because the statement

Set oRow = oTbl.Rows(rowNum)

does involve counting from the top of the table, plus an object
assignment.

Feel free to run some tests and report the results.

I'd rather bow to your judgement.

Learnt something from that today already!

Greetinx
Robert

OK, Mr. Idle Curiosity, here are some test data for tables of several
sizes:

nRows T1(sec) T2(sec)
500 9.5 12.1
800 24.1 30.9
1200 58.7 75.0
1500 93.4 118.6

Plotting these in Excel and applying trendlines, it's clear that both
series are quadratic (that is, the time is a function of the square of
the number of rows). The first example is about 30% faster than the
second one, but they both chew up a lot of processor time.

In fact, on this system (Athlon 2800 with 2 GB RAM) both macros cause
the CPU utilization to jump up to 100% and stay there the whole time.
That causes the CPU temperature to go from 55C to 60C, which makes me
rather unhappy... I don't want to try your 10 000 rows for fear of
frying the system.

The code used for the tests was this, in which the "processing" of
each row was just to make the text bold in the first cell:

Sub test()
Const nRows = 1500
Dim sampleDoc As Document
Dim startTime As Single, stopTime As Single
Dim sample1Time As Single, sample2Time As Single
Dim msg As String

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample1(sampleDoc)
stopTime = Timer
sample1Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

MsgBox "Finished part 1"

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample2(sampleDoc)
stopTime = Timer
sample2Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

msg = "For Each took " & sample1Time & " sec" & vbCr
msg = msg & "For rowNum took " & sample2Time & " sec"
MsgBox msg
End Sub

Sub sample1(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Set oTbl = oDoc.Tables(1)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
oRow.Cells(1).Range.Bold = True
End If
Next
End Sub

Sub sample2(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = oDoc.Tables(1)
' start processing at row 2
For rowNum = 2 To oTbl.Rows.Count
Set oRow = oTbl.Rows(rowNum)
oRow.Cells(1).Range.Bold = True
Next
End Sub

Function MakeTable(nRows As Long) As Document
Dim oDoc As Document
Dim idx As Long
Dim txt As String
Dim r As Range
Const s = "Hello" & vbTab

Set oDoc = Documents.Add
For idx = 1 To nRows
txt = txt & s & CStr(idx) & vbCr
Next
Set r = oDoc.Range
With r
.Collapse wdCollapseStart
.Text = txt
.MoveEnd wdCharacter, -1
.ConvertToTable
End With

Set MakeTable = oDoc
End Function

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

 

[Doug Robbins - Word MVP]

Of course a probably quicker way of achieving what your test was doing would
be to use:

Dim arange As Range
With ActiveDocument.Tables(1)
Set arange = .Cell(2, 1).Range
arange.End = .Cell(.Rows.Count, 1).Range.End
arange.Select
Selection.Font.Bold = True
End With

<g,d & r>

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Jay Freedman wrote:
[..]

I don't know for sure. It depends on how MS implemented the .Index
property. If the runtime has to count from the top of the table each
time to get that number, it could get very slow for very large tables.
The comparison is trivial compared to that.

Hmm, this is no array with fixed dimensions where one would presume the
same amount of time to fetch a[100] compared to a[1], right.

For the same table, though, I think the second example would be even
worse because the statement

Set oRow = oTbl.Rows(rowNum)

does involve counting from the top of the table, plus an object
assignment.

Feel free to run some tests and report the results.

I'd rather bow to your judgement.

Learnt something from that today already!

Greetinx
Robert

OK, Mr. Idle Curiosity, here are some test data for tables of several
sizes:

nRows T1(sec) T2(sec)
500 9.5 12.1
800 24.1 30.9
1200 58.7 75.0
1500 93.4 118.6

Plotting these in Excel and applying trendlines, it's clear that both
series are quadratic (that is, the time is a function of the square of
the number of rows). The first example is about 30% faster than the
second one, but they both chew up a lot of processor time.

In fact, on this system (Athlon 2800 with 2 GB RAM) both macros cause
the CPU utilization to jump up to 100% and stay there the whole time.
That causes the CPU temperature to go from 55C to 60C, which makes me
rather unhappy... I don't want to try your 10 000 rows for fear of
frying the system.

The code used for the tests was this, in which the "processing" of
each row was just to make the text bold in the first cell:

Sub test()
Const nRows = 1500
Dim sampleDoc As Document
Dim startTime As Single, stopTime As Single
Dim sample1Time As Single, sample2Time As Single
Dim msg As String

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample1(sampleDoc)
stopTime = Timer
sample1Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

MsgBox "Finished part 1"

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample2(sampleDoc)
stopTime = Timer
sample2Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

msg = "For Each took " & sample1Time & " sec" & vbCr
msg = msg & "For rowNum took " & sample2Time & " sec"
MsgBox msg
End Sub

Sub sample1(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Set oTbl = oDoc.Tables(1)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
oRow.Cells(1).Range.Bold = True
End If
Next
End Sub

Sub sample2(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = oDoc.Tables(1)
' start processing at row 2
For rowNum = 2 To oTbl.Rows.Count
Set oRow = oTbl.Rows(rowNum)
oRow.Cells(1).Range.Bold = True
Next
End Sub

Function MakeTable(nRows As Long) As Document
Dim oDoc As Document
Dim idx As Long
Dim txt As String
Dim r As Range
Const s = "Hello" & vbTab

Set oDoc = Documents.Add
For idx = 1 To nRows
txt = txt & s & CStr(idx) & vbCr
Next
Set r = oDoc.Range
With r
.Collapse wdCollapseStart
.Text = txt
.MoveEnd wdCharacter, -1
.ConvertToTable
End With

Set MakeTable = oDoc
End Function

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

 

[Tony Jollans]

Interesting - I had to join the curious and find out more. I repeated your
tests on my somewhat slower machine and got the same trends. I also added
another one ...

Sub sample3(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = oDoc.Tables(1)
' start processing at row 2
Set oRow = oTbl.Rows(2)
' and loop till end
Do While Not oRow Is Nothing
oRow.Cells(1).Range.Bold = True
Set oRow = oRow.Next
Loop
End Sub

This was marginally faster but still showed the same degradation.

I then changed it to (a) look at the last 100 rows of the table only and (b)
to do nothing else

Sub sample3(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = oDoc.Tables(1)
' process last 100 rows
Set oRow = oTbl.Rows(oTbl.Rows.Count - 99)
Do While Not oRow Is Nothing
Set oRow = oRow.Next
Loop
End Sub

Number or Rows in Table/time taken

500 / 0.730 sec
1000 / 1.851 sec
2000 / 3.598 sec
5000 / 9.254 sec
10000 / 19.036 sec

This looks like a linear relationship to me but there does seem to be
inherent poor performance in large tables no matter what the actual
processing. From brief further checks, which need confirmation, it appears
that the actual bolding (in this case) is what propels it to a square
relationship.

--
Enjoy,
Tony

Jay Freedman wrote:
[..]

I don't know for sure. It depends on how MS implemented the .Index
property. If the runtime has to count from the top of the table each
time to get that number, it could get very slow for very large tables.
The comparison is trivial compared to that.

Hmm, this is no array with fixed dimensions where one would presume the
same amount of time to fetch a[100] compared to a[1], right.

For the same table, though, I think the second example would be even
worse because the statement

Set oRow = oTbl.Rows(rowNum)

does involve counting from the top of the table, plus an object
assignment.

Feel free to run some tests and report the results.

I'd rather bow to your judgement.

Learnt something from that today already!

Greetinx
Robert

OK, Mr. Idle Curiosity, here are some test data for tables of several
sizes:

nRows T1(sec) T2(sec)
500 9.5 12.1
800 24.1 30.9
1200 58.7 75.0
1500 93.4 118.6

Plotting these in Excel and applying trendlines, it's clear that both
series are quadratic (that is, the time is a function of the square of
the number of rows). The first example is about 30% faster than the
second one, but they both chew up a lot of processor time.

In fact, on this system (Athlon 2800 with 2 GB RAM) both macros cause
the CPU utilization to jump up to 100% and stay there the whole time.
That causes the CPU temperature to go from 55C to 60C, which makes me
rather unhappy... I don't want to try your 10 000 rows for fear of
frying the system.

The code used for the tests was this, in which the "processing" of
each row was just to make the text bold in the first cell:

Sub test()
Const nRows = 1500
Dim sampleDoc As Document
Dim startTime As Single, stopTime As Single
Dim sample1Time As Single, sample2Time As Single
Dim msg As String

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample1(sampleDoc)
stopTime = Timer
sample1Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

MsgBox "Finished part 1"

Set sampleDoc = MakeTable(nRows)
startTime = Timer
Call sample2(sampleDoc)
stopTime = Timer
sample2Time = stopTime - startTime
sampleDoc.Close savechanges:=wdDoNotSaveChanges
Set sampleDoc = Nothing

msg = "For Each took " & sample1Time & " sec" & vbCr
msg = msg & "For rowNum took " & sample2Time & " sec"
MsgBox msg
End Sub

Sub sample1(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Set oTbl = oDoc.Tables(1)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
oRow.Cells(1).Range.Bold = True
End If
Next
End Sub

Sub sample2(oDoc As Document)
Dim oTbl As Table
Dim oRow As Row
Dim rowNum As Long
Set oTbl = oDoc.Tables(1)
' start processing at row 2
For rowNum = 2 To oTbl.Rows.Count
Set oRow = oTbl.Rows(rowNum)
oRow.Cells(1).Range.Bold = True
Next
End Sub

Function MakeTable(nRows As Long) As Document
Dim oDoc As Document
Dim idx As Long
Dim txt As String
Dim r As Range
Const s = "Hello" & vbTab

Set oDoc = Documents.Add
For idx = 1 To nRows
txt = txt & s & CStr(idx) & vbCr
Next
Set r = oDoc.Range
With r
.Collapse wdCollapseStart
.Text = txt
.MoveEnd wdCharacter, -1
.ConvertToTable
End With

Set MakeTable = oDoc
End Function

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

 

[Jay Freedman]

Hi Doug,

Of course, that would be fine if making column 1 bold was the real
task (or anything else simple like that). And I did note your <g, d &
r> ...

For anyone else wondering what's going on, the general question that
started the thread involved some arbitrary "process each row after the
second one," so the loop might have to examine each row and do
something different depending on what was found there.

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

 

[Jay Freedman]

Thanks, Tony. It's always good to have another technique, but a bit
dismaying that they're all of roughly the same inefficiency.

I think the basic problem is that VBA's built-in collections don't
have good indexers. In a language such as C++ or C#, collections are
built on top of hash tables, and the access algorithm is order-zero
(the time is constant, not dependent on the number of items). It looks
like VBA collections use an order-one algorithm built on arrays. :-(

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