Maximising the performance of Word tables

1.

As a user

2.

In code

To return to top, press Ctrl+Home, or use Alt + Left Arrow to
Go Back)

1.

Working in Normal view when you can helps, especially if you turn off “Background repagination” (Tools + Options + General).
Whatever you do, though, tables in Word 2000 and higher are a lot slower in
most respects than
in Word 97 – an unfortunate by-product of the new table engine created so that
Word tables could be fully HTML-compatible.

2.

If using Word 2000 and above, select Table | Table Properties | Options,
and turn off the checkbox: “Automatically resize to fit contents”.
As well as slowing tables down considerably, this setting gives (usually)
undesirable results, but unfortunately is automatically switched on in all new tables.

3.

Don't create a single row containing a large amount of text.
I have seen many tables containing rows which (with non-printing
characters displayed) look something like this:

Figure 1: A badly laid out table row (shown with non-printing characters

and table gridlines visible)

Apart from anything else, laying out table
text as shown above makes it a complete nightmare to get everything to line
up, which defeats the object of using a table in the first place, the great
strength of tables being that they line text up automatically if used
properly. But in addition, rows containing many paragraphs slow tables down.

So
create a separate row for each logical element of the table, as shown in
Figure 2. Note that if you don't want horizontal borders
between some of the rows, you don't have to have them; so not wanting borders
is not a reason to add paragraphs instead of adding rows:

Figure 2: How the row in Figure 1 should have been laid out –

as six separate rows, but with no horizontal border between the rows

4.

Break long tables up (use several smaller tables rather than
one very long one), separating the “sub-tables” with headings. So
rather than, for instance, creating something like this:

Figure 3: Avoid putting your headings inside your tables

... split your
tables up into logical sub-tables instead, putting your headings outside the
tables (using Heading styles), like this:

Figure 4: Put your headings outside your tables

5.

Avoid using merged cells as much as possible: wherever you
can get away with it, remove unwanted borders
instead.

6.

In Word 2000 and above, use “text-wrapped” tables
only when really necessary. Set “text-wrapping” to “None”
whenever you can. For more on text-wrapped tables, see: Table basics.

7.

In Word 2000 and above, if your tables contain graphics,
make them inline where possible.

1.

If using Word 2000 and above, turn off “Automatically resize to fit
contents” for all tables:

Selection.Tables(1).AllowAutoFit = False

Whatever you do, though, tables in Word 2000 and higher are a lot slower in
most respects than
in Word 97 – an unfortunate by-product of the new table engine created so
that Word tables could be fully HTML-compatible (but see 5. and 7.
below for an astonishing exception to this rule).

2.

2–7 above apply to tables created with code as well. In the case of
switching views and turning off background repagination, it is polite to the
user to leave their settings as you found them, i.e.:

Dim ViewType As
Long, PaginationSetting As Boolean

ViewType = ActiveWindow.View.Type

PaginationSetting = Options.Pagination

'rest of your code

Options.Pagination = PaginationSetting

ActiveWindow.View.Type = ViewType

Unfortunately, even with ScreenUpdating switched off, the screen flickers
when you change views. The only way to prevent this is to use the the LockWindowUpdate API (which is
beyond the scope of this article, but a Google search will turn up details on
it). So it's only worth bothering to change views for large tables.

3.

If you are putting data into a Word table using code (e.g.
if you are reading it from a database), you will get much better performance if
you initially put the data into the Word document as tab-delimited text, and
then convert the text to a table at the very end. For example (the following
code sample requires you to set a reference to DAO,
and also assumes you have the Northwind sample database installed – it is one
of the sample databases supplied with Office, so if it is not already
installed on your system, you can re-run Setup in order to install it ):

Sub GetDataIntoTable()



Dim db As Database,
rs As Recordset, MyRange As
Range, i As Integer

Set db = OpenDatabase(Name:= _

"c:\program files\microsoft
office\office\samples\northwind.mdb")

Set rs = db.OpenRecordset(Name:="Shippers")



Set MyRange = ActiveDocument.Content

MyRange.Collapse wdCollapseEnd



MyRange.InsertAfter Text:=rs.Fields(1).Name & vbTab &
rs.Fields(2).Name & vbCr

Set MyRange = ActiveDocument.Content

MyRange.Collapse wdCollapseEnd

For i = 0 To
rs.RecordCount - 1

'Insert the data as tab-delimited text


MyRange.InsertAfter Text:=rs.Fields(1).Value & vbTab
& rs.Fields(2).Value & vbCr

rs.MoveNext

MyRange.Collapse Direction:=wdCollapseEnd

Next i

rs.Close

db.Close




'Now convert to table


MyRange.Start = ActiveDocument.Range.Start

MyRange.ConvertToTable



Set db = Nothing

Set rs = Nothing



End Sub

If some cells in your table need to contain more than one paragraph (or to
contain manual line breaks), separate those “paragraphs” or “lines”, initially, with a dummy delimiter such as a comma or a
dollar sign; and then do a Find and Replace at the end (after converting the
text to a table), to replace the delimiter with a paragraph mark or manual
line break, as desired. For a code sample that illustrates this technique,
see: How to generate a table of samples of every font on your system.

4.

If for some reason you can't insert your text in
tab-delimited format and convert to table at the end, then don't build
up your table as you go by adding a row at a time. Instead, work out in
advance the total number of rows that you'll need (e.g. by reading all your values
into an array before inserting any of them in the document) and then create
the entire table in one go; e.g.:

Set oTable = ActiveDocument.Tables.Add(Range:=MyRange, _

Numrows:=1000, numcolumns:=4)

'Word 2000 only:

oTable.AllowAutoFit = False

5.

If inserting text, use ranges rather than selections
(as illustrated in the above code sample): and also, use characters such as
vbCr and vbTab to allow you insert as much text as possible with a single
statement – again, as illustrated in the above code sample. For instance:

MyRange.InsertAfter Text:=rs.Fields(1).Value & vbTab
& rs.Fields(2).Value & vbCr

... runs much faster than:

Selection.TypeText Text:=rs.Fields(1).Value

Selection.TypeText Text:=vbTab

Selection.TypeText Text:=rs.Fields(2).Value

Selection.TypeParagraph

6.

If inserting a large amount of text into the document, make
sure background spelling and grammar checking are switched off.

At the end of your macro,
out of politeness to the user, switch the settings back on if they were on to
start with. Also, if you know that the inserted text won't need to be spelling
or grammar checked, you can mark the inserted range as already checked,
without marking the rest of the document. (Thanks to Greg Chapman for this
tip).

Dim SpellSetting As
Boolean, GrammarSetting As Boolean, _

MyRange As
Range



SpellSetting = Options.CheckSpellingAsYouType

GrammarSetting = Options.CheckGrammarAsYouType



With Options

.CheckSpellingAsYouType = False

.CheckGrammarAsYouType = False

End With



'Insert your text, e.g.

Set MyRange = Selection.Range

Selection.InsertFile "C:\Temp\Temp.doc"

'Or insert it from a database, whatever



'If you know that it's safe to do so, mark the inserted text as already checked,

'but don't
mark the text that you didn't insert. If inserting from a database,

'set a range to the inserted text
and operate on that range.

'If using Selection.InsertFile, use the following:



MyRange.End = Selection.End

With MyRange

.SpellingChecked = True

.GrammarChecked = True

End With



'Rest of your code, and then at the very end:

With Options

.CheckSpellingAsYouType = SpellSetting

.CheckGrammarAsYouType = GrammarSetting

End With

7.

Applying manual formatting is very resource-hungry –
apply predefined styles instead.

8.

When cycling through table cells, never refer to a table
cell by its coordinates; as that is horrendously
slow, because it forces Word to calculate from
scratch, for every single cell, where in the document the cell in question
actually is.

And don't move selection from cell to cell, as this will also slow your
code down dramatically.

Whilst it is much faster to cycle through the Cells collection, as in:

Sub
OperateOnEveryCellUsingTableObject()



Dim oCell As Cell

For Each oCell In Selection.Tables(1).Range.Cells

oCell.Range.Text = "Hi there"

Next oCell



End Sub

... a much faster method still (with screen updating switched off) is to select
the table, in code, and then cycle through the cells within the selection – don't ask me why this
should be faster, but it is:

Sub OperateOnEveryCellInSelectedTable()



Dim oCell As Cell



Application.ScreenUpdating = False

Selection.Tables(1).Select

For Each oCell In Selection.Cells

oCell.Range.Text = "Hi there"

Next oCell

Application.GoBack

Application.ScreenUpdating = True



End Sub

When I timed the above macros in Word 97 and in Word 2000, using a 350-row,
5-column table, the results were very interesting (I've rounded the results to
1 decimal place):

The above results were obtained with a table created in Word 97. If the
table was created in Word 2000 (and if AllowAutoFit
was switched off), then using the Table object became significantly faster in
Word 2000 (though not in 97); but was still far slower than using a Selection object. If the
document was created in Word 2000 and then saved in Word 97, the results were similar
to the above.

I have no theories to explain these results, but they are easy to
reproduce. Tests by colleagues who have
access to Word 2002 gave broadly similar results to Word 2000.

Turning off screen updating made no difference to the speed of the OperateOnEveryCellUsingTableObject()
macro, although it dramatically speeded up the OperateOnEveryCellInSelectedTable()
macro.

9.

When operating on specific rows, or comparing the contents
of adjacent rows, use the Row object, as in the code samples at Deleting duplicate rows in a table.

10.

If you want to operate on the cells in a specific table
column, you can't cycle through the cells within the column's Range –
Ranges and Columns simply don't mix. Crazily, a table column's Range contains
many cells that are not actually within the column. This must once have seemed
like a good idea to someone at Microsoft, probably because they were suffering
from a bad hangover at the time! There is a certain pedantic logic to it: a
column's range contains all the cells starting from the top of the column,
moving through the table from left to right along each row, until you get to
the bottom of the column. From a usability perspective this was a
nightmarish design decision, though, and well worth emailing mswish@microsoft.com
about.

By far the fastest way of operating on a specific column is to select it
and then cycle though the selected cells, as in:

Sub OperateOnSelectedColumn3()



Dim oCell As
Cell

Application.ScreenUpdating = False

'Select the third cell in the first row of the
table


Selection.Tables(1).Cell(1, 3).Select

'Select column 3


Selection.SelectColumn

'Operate on the cells in column 3


For Each oCell In Selection.Cells

oCell.Range.Text = "Hi there"

Next oCell

Application.GoBack

Application.ScreenUpdating = True



End Sub

Note that you cannot safely use the Columns object to specify which
column you want to select, as in:

Selection.Tables(1).Columns(3).Select

.. because that gives an error message: “Cannot access individual columns in this collection because the table has mixed cell
widths”, either if there are any merged cells,
or even if any cell anywhere in the table has a
slightly different width than the rest of the cells in the same column!
So for all practical purposes, the Column object is completely useless –
another design decision resulting from far too many Tia Marias laced
with vodka, and well worth an email to mswish@microsoft.com.

If there might be merged cells in row 1 of the table, you could select the
third cell in the last row of the table instead, and then select the
column, rather than using the first row:

Dim oRow As
Row, oCell As Cell

Application.ScreenUpdating = False


Set oRow = Selection.Tables(1).Rows.Last

oRow.Cells(3).Select

Selection.SelectColumn


For Each oCell In Selection.Cells

oCell.Range.Text = "Hi there"


Next oCell

Application.GoBack

Application.ScreenUpdating = True

Instead of selecting the column you could cycle through every cell
in the table,
operating on those cells whose ColumnIndex property matches the column
you want, as follows:

Sub OperateOnColumn3UsingRanges()



Dim oCell As
Cell

For Each oCell In
Selection.Tables(1).Range.Cells

If oCell.ColumnIndex = 3
Then

oCell.Range.Text = "Hi
there"

End If

Next oCell



End Sub

... but this is not only much slower than selecting the column, but
also, if there are any horizontally merged cells in the table, the
ColumnIndex property gives undesirable results.

When I timed the above macros in Word 97 and in Word 2000, using a 350-row,
5-column table, the results on my machine were as follows (I've rounded the
results to 1 decimal place):

The above results were obtained with a table created in Word 97. If the
table was created in Word 2000 (and if AllowAutoFit
was switched off), then the OperateOnColumn3UsingRanges
macro became significantly faster in Word 2000 (though not in 97); but was still far slower than using a
Selection object. If the document was created in Word 2000 but then saved in Word
97, the results were similar to the above.

As with 7. above, I have no theories to explain these results, but they are easy to
reproduce. Tests by colleagues who have
access to Word 2002 gave broadly similar results to Word 2000; and again, turning off screen updating made no difference
to the speed of the OperateOnColumn3UsingRanges()
macro, although it dramatically speeded up the OperateOnSelectedColumn3()
macro.

11.

If formatting the borders and shading of a table, it is far
more efficient, and can speed up your code dramatically (even in Word 97), if
you execute the built-in FormatBordersAndShading dialog
(without displaying it), than it is to use “native VBA” Methods to do the formatting. This
trick also greatly reduces the risk of getting “Formatting too complex” error messages.

In essence, this is
because you can execute many commands simultaneously using the dialog,
whereas, using VBA methods, you have to execute one statement at a time, and
wait for one to finish before the next can start.

For a more detailed discussion of the principles behind this, and for some code
samples to get you started, see #2 at Getting help with calling Word's built-in dialogs using VBA (and why doing so can be much more useful than you'd think),
in the section: “Why use built-in dialogs?”.

12.

If doing a great deal of formatting of tables, then even all
of the above tricks combined may not prevent you from getting the odd “Formatting too complex” error message.