How to speed up merging tables? (with code sample)

[Benjamino5]

Hi all,

I'm working on a project that loops through this chunk of code dozens or
hundreds of times, and it's taking a long time to complete. Any ideas about
how to do this faster? I'm merging each row in the table and then calling a
find and replace operation in certain circumstances. Here's the code.

(By the way, the reason I'm declaring everything as a generic object is that
this code is inside a VB.Net project that late-binds to Word. That's another
slow-down: when this code runs inside an instance of Word instead of calling
Word through automation, it's much faster. But I have to keep my project in
VB.Net, )

_______________________

Sub MergeTable(ByRef oTable As Object)
' merge all cells on each row together
' then handle one of the following situations:
' 1) the row is NOT metadata, so replace the first para mark (put in
by merging) with a tab
' 2) the row is metadata, and originally "Ans:X" was in its own
cell, so there's no para mark
' to worry about removing (the next metadata field will have been
put on its own line, but that's
' what we want
' 3) the row is metadata, but the file was set up so that "Ans:" was
its own cell; so we need
' to remove the first para mark so "Ans:" and the answer are
together on the same line
Dim i As Integer
With oTable
For i = 1 To .rows.count
.rows(i).Cells.Merge()
' chr(13) is a para mark
' if the row has "Ans:[para mark]" or DOESN'T have "Ans:" at
all, then call ParaMarkToTab
If InStr(.rows.item(i).range.text, "Ans:" & Chr(13)) <> 0 Or
InStr(.rows.item(i).range.text, "Ans:") = 0 Then
Call ParaMarkToTab(.rows.item(i).Cells.item(1))
End If
Next i
End With
End Sub

Sub ParaMarkToTab(ByRef oCell As Object)
' turns the extra paragraph mark made when merging cells into a tab
' it stops after the first one, so it won't mess up any existing
paragraph marks
oCell.range.Find.Execute(FindText:="^p", Replacewith:="^t")
End Sub

 

[Jonathan West]

You can speed up by doing the following

Change this

Dim i As Integer
With oTable
For i = 1 To .rows.count
.rows(i).Cells.Merge()
' chr(13) is a para mark
' if the row has "Ans:[para mark]" or DOESN'T have "Ans:" at
all, then call ParaMarkToTab
If InStr(.rows.item(i).range.text, "Ans:" & Chr(13)) <> 0 Or
InStr(.rows.item(i).range.text, "Ans:") = 0 Then
Call ParaMarkToTab(.rows.item(i).Cells.item(1))
End If
Next i
End With


to this

Dim oRow As Object
With oTable
For oRow in oTable.Rows
oRow.Cells.Merge()
' chr(13) is a para mark
' if the row has "Ans:[para mark]" or DOESN'T have "Ans:" at
all, then call ParaMarkToTab
If InStr(oRow.range.text, "Ans:" & Chr(13)) <> 0 Or
InStr(oRow.range.text, "Ans:") = 0 Then
Call ParaMarkToTab(oRow.Cells.item(1))
End If
Next i
End With

That should speed things up as you are setting an object reference to the
current row, rather than referencing it by index every time.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Hi all,

I'm working on a project that loops through this chunk of code dozens or
hundreds of times, and it's taking a long time to complete. Any ideas
about
how to do this faster? I'm merging each row in the table and then calling
a
find and replace operation in certain circumstances. Here's the code.

(By the way, the reason I'm declaring everything as a generic object is
that
this code is inside a VB.Net project that late-binds to Word. That's
another
slow-down: when this code runs inside an instance of Word instead of
calling
Word through automation, it's much faster. But I have to keep my project
in
VB.Net, )

_______________________

Sub MergeTable(ByRef oTable As Object)
' merge all cells on each row together
' then handle one of the following situations:
' 1) the row is NOT metadata, so replace the first para mark (put
in
by merging) with a tab
' 2) the row is metadata, and originally "Ans:X" was in its own
cell, so there's no para mark
' to worry about removing (the next metadata field will have been
put on its own line, but that's
' what we want
' 3) the row is metadata, but the file was set up so that "Ans:"
was
its own cell; so we need
' to remove the first para mark so "Ans:" and the answer are
together on the same line
Dim i As Integer
With oTable
For i = 1 To .rows.count
.rows(i).Cells.Merge()
' chr(13) is a para mark
' if the row has "Ans:[para mark]" or DOESN'T have "Ans:"
at
all, then call ParaMarkToTab
If InStr(.rows.item(i).range.text, "Ans:" & Chr(13)) <> 0
Or
InStr(.rows.item(i).range.text, "Ans:") = 0 Then
Call ParaMarkToTab(.rows.item(i).Cells.item(1))
End If
Next i
End With
End Sub

Sub ParaMarkToTab(ByRef oCell As Object)
' turns the extra paragraph mark made when merging cells into a tab
' it stops after the first one, so it won't mess up any existing
paragraph marks
oCell.range.Find.Execute(FindText:="^p", Replacewith:="^t")
End Sub

 

[Benjamino5]

Jonathan,

Thank you! I didn't realize using an object reference was faster than using
the index. Is that true across the board in Word, or does it depend on the
object?

Ben

 

[Jonathan West]

Jonathan,

Thank you! I didn't realize using an object reference was faster than
using
the index. Is that true across the board in Word, or does it depend on the
object?

Generally it is faster to set an object reference, especially if the
collection has a large number of items in it. Although a lot of things in
the Word object model are set up as collections, internally they aren't set
up as linked lists, so getting at an item in the collection with a high
index number requires that Word counts its way through the items in the
collection until it comes to the right one. That takes time. If you set an
object reference, the counting through only has to be be done once instead
of every time you access a property of the item.

A general principle of Office programming in VBA or VB.NET when you are
heavily using the object model is to minimise the number of dots executed.
With your original code,if you count the number of dots you pass in one
iteration of the loop (including implicit ones referencing an item in a
collection) you execute 18 dots. My version of the code reduces that to 9.
The number of dots is a measure of the number of times you have to drill
down into the object model. Each bit of drilling is extra time taken.

You can reduce the number of dots by various means, including

- Specifying object variables as needed
- Using For Each-Next loops wherever possible
- Using With-End With constructions

Of course, there are many other measures that can be taken in code to
improve performance, but this one seemed the most likely to make a
difference in this particular case.

 

[Benjamino5]

Jonathan,

Just wanted to let you know that I've taken your advice to heart not only in
this code but more generally, and I'm seeing significant speed-ups.

Thanks again!
Ben