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.