Insights

Categories

Word and Visual Basic for Applications (VBA): Worth the Effort? (Part 3)

Jan 29, 2016

This post is part of a series on VBA for Microsoft Word. If you haven’t read the earlier parts, you can find them here: Part 1 and Part 2.

In the previous post in this series, we looked at VBA fundamentals, such as writing to and reading from a document, and looping through all the paragraphs in a document. In this post, we build on earlier concepts to demonstrate some basic, but more practical auto-formatting examples.

Automating Tasks for Improved Efficiency and Accuracy

We can use VBA to perform repetitive tasks automatically. When we automate tasks in this way, we improve accuracy because the task is always done in the same way (there is no possibility for manual error) and we improve efficiency because we can use the time saved to focus on more creative and challenging tasks. With just a few lines of code, it is possible to save a significant amount of time.

Repeating Table Heading Rows

Suppose you are formatting a document that contains 200 tables. You want to ensure that the first row of each table is a heading row so that the heading row repeats at the top of the new page if a table spills to a new page. You could select the Repeat as header row at the top of each page checkbox for each table manually, but because there are 200 tables, that could take some time, not to mention the possibility of missing a table or two.

An alternative is to write and run a small Word VBA macro similar to the ones given in the previous post. For example:

Sub MakeTableHeadingRowsRepeat()
     With ActiveDocument
         For n = 1 To .Tables.Count
             .Tables(n).Rows(1).HeadingFormat = True
         Next n
     End With
 End Sub

A few things to note:

  • With … End With is a VBA statement combination that allows you to easily reference the methods and properties of an object within the context of the With statement using ‘dot’ notation. Dot notation means that the .Tables.Count property takes its context from the object referenced in the With statement. This means that the .Tables.Count property is really ActiveDocument.Tables.Count, which represents the number of tables in the active document.
  • Tables is similar to Paragraphs. It is the collection (or group) of all tables in the document. Tables(n) is the nth table so, for example, Tables(57) is the 57th table in the document.
  • For … Next is the VBA statement combination shown in the earlier post, but in a slightly different form. Here, you explicitly say ‘Loop while the variable n is between 1 and the total number of tables in the document.’
  • .Tables(n).Rows(1).HeadingFormat = True states that on the nth loop, in the nth table, set the HeadingFormat property of the first row (Rows(1)) to a value of True. This is equivalent to selecting the Repeat as header row at the top of each page checkbox for the first row of the nth table.

You can validate that this script works correctly. In your test document, choose Insert > Table and create a table that has three columns and four rows. Select the first row, then right-click the row and choose Table Properties. In the Row tab, verify that the Repeat as header row at the top of each page checkbox is not selected. Copy and paste the table a number of times so that your document has several tables.

With the cursor inside the subroutine, click (Run Sub/UserForm) on the toolbar (or from the menu, choose Run > Run Sub/User Form).

Result: There is no visual indication that anything has changed. However, select the first row in any table and choose Table Properties. Click the Row tab and note that the Repeat as header row at the top of each page checkbox is now selected. If any of these tables spill over to a new page, the heading row repeats automatically.

Auto Formatting Table Heading Rows

Let’s take this a step further. With the addition of one line of code (highlighted in the following code snippet), you can also make sure that each cell in the first row of the table (the heading row) is formatted with the correct style. For this example, let’s say that style is Heading 2 (it’s not very realistic, but a convenient way to demonstrate the point). Modify the code as shown below:

Sub MakeTableHeadingRowsRepeat()
     With ActiveDocument
         For n = 1 To .Tables.Count
             .Tables(n).Rows(1).HeadingFormat = True
             .Tables(n).Rows(1).Range.Style = "Heading 2"
         Next n
     End With
 End Sub

What does this new line do? On the nth loop through the For … Next statement, for the first row in the nth table, set the Range.Style property to a value of "Heading 2". This effectively says, for all the cells in row 1, set the paragraph style to Heading 2.

With the cursor inside the subroutine, click (Run Sub/UserForm) on the toolbar.

Result: The cells in the first row of each table are now formatted with a blue colour, which is the default colour of Heading 2. If you click in one of these cells and open the style gallery, you will see that Heading 2 is applied.

At this point you should get a sense of just how powerful these macros can be. The key of course is knowing which objects, methods, and properties to use, but that becomes easier as you develop more macros and explore more of Word’s Document Object Model (DOM).

To sum up, in this post, you’ve seen how Word VBA can be useful for automating relatively simple repetitive tasks. In the next post, we will introduce Word’s macro recording feature which generates code for you. By combining recorded code with your own code, you can create quite complex macros to automate more substantial tasks, such as automatically applying styles.

Share This

Share This

Share this post with your friends!