Using VBA to Format Long Documents in the 2007 Office System in a Fraction of the Time
Summary: Introduces and demonstrates how to use basic loops, conditional statements, and grouping statements to format long documents with just a handful of Microsoft Visual Basic for Applications (VBA) code. (8 printed pages)
Stephanie Krieger, arouet.net, Microsoft Office MVP
Applies to: Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, Microsoft Office Word 2007, and Microsoft Visual Basic for Applications.
If you create reports, presentations, or other long documents, chances are that you have had the tedious task of reformatting those documents at one time or another.
Like many Microsoft Office users, you might paste content from other sources as-is and decide to clean it up later (and then find, as deadlines loom, that you don't have the time to do it). Or, maybe you work on a project team where it seems that the more editors you have on a presentation the more inconsistent the formatting becomes. Regardless, if you've had the task of fixing a poorly formatted document or changing the formatting of a long document in the 11th hour, you have probably wondered if there is a faster way. Well, wonder no more.
This article walks you through examples of how to edit your recorded Microsoft Visual Basic for Applications (VBA) macros and add a few simple lines of code that can enable you to format long documents and presentations in no time.
This article assumes that you are familiar with how to access the Microsoft Visual Basic Editor (VBE) and that you have some familiarity with the basics of reading VBA code. If you are new to VBA, check out Taking the 2007 Office System Further with VBA and Open XML Formats for an introduction to the difference between recording and writing VBA macros.
If you have recorded macros, you know that you can record a number of tasks in a single macro, but that recorded macro can only act on your selection. When formatting long documents, recorded macros can be very tedious to use because you have to manually run the macro to after selecting each individual item (such as a paragraph or a table).
Instead, when you write a macro (or edit a macro that you record) your macro can act on any document content you specify without having to first select that content. In fact, you can even add just a few lines of code (known as a loop) that enable you to run the macro just once for all instances of the object type that you need to format.
For example, in the article referenced earlier (Taking the 2007 Office System Further with VBA and Open XML Formats) you saw the following recorded and edited versions of the same code for formatting a table.
To use the recorded version shown here, you need to click into each table in the document and then run the macro.
Sub FormatMyTables() Selection.Tables(1).Style = "Light Shading - Accent 4" Selection.SelectRow Selection.Style = ActiveDocument.Styles("Heading 2") Selection.Tables(1).Select Selection.Tables(1).AutoFitBehavior (wdAutoFitFixed) Selection.Tables(1).Rows.Alignment = wdAlignRowCenter Selection.Columns.PreferredWidthType = wdPreferredWidthPoints Selection.Columns.PreferredWidth = InchesToPoints(0.6) End Sub
The following edited version of the macro adds a loop that enables you to run the macro once and it automatically loops through the collection of all tables in the document.
Sub FormatMyTables() Dim oTb As Table For Each oTb In ActiveDocument.Tables oTb.Style = "Light Shading - Accent 4" oTb.Rows(1).Range.Style = ActiveDocument.Styles("Heading 2") oTb.AutoFitBehavior (wdAutoFitFixed) oTb.Rows.Alignment = wdAlignRowCenter oTb.Columns.PreferredWidth = InchesToPoints(0.6) Next oTb End Sub
Let's break down the preceding macro to see how it's done:
Use the Dim statement to declare a variable. In VBA, variables are used for many purposes and many types of data. In this case, an object variable is declared to represent a table object, because the macro is going to loop through the collection of all tables in the document. A variable name is an alphanumeric identifier that begins with a letter, may contain underscores, but does not contain spaces and does not duplicate a built-in VBA term. The best practice for selecting variable names is to pick a concise name that reflects the purpose of the variable.
Declaring the object variable also enables VBA to recognize that variable as the particular type of object. So, for example, when you type a period after oTb in this macro, you get the AutoComplete list (also known as IntelliSense) of available properties and methods for the Word Table object.
The For Each … Next statement is the loop that wraps around the set of actions you want to perform on each table in the document.
The For Each portion of the statement states that for each table in the collection of all tables in this document, you want to perform the indicated set of actions.
The Next statement marks the end of the loop. Here it appears below the set of actions to tell the code to move to the next table in the collection and start the set of actions again. The loop automatically ends after the actions are taken on the last object in the collection.
To try the macros in this article for yourself, open the VBE and then, on the Insert menu click Module to create a code module into which you can paste the code from this article.
How Many Ways Can You Loop?
In the previous example, you saw how just three lines of code (one for the variable declaration and two for the loop) can potentially save you hours of work. So, you are probably already pretty happy with the idea of using VBA to format your documents. But, how would you like to save even more time and get even more flexibility?
The For Each…Next statement loops through all objects in a collection. Following are other common types of loops that you can use to run your code automatically on multiple objects with even more flexibility:
For…Next: Instead of acting on each object in a collection, a For…Next statement enables you to specify the objects that you want to act on by using a counter. For example, you may want to loop through all but the first five tables in the document or only odd numbered tables.
Do … Loop: A Do…Loop statement has several variations that enable you to loop through objects either while a statement is true or until a statement becomes true. For example, if your document is not allowed to exceed a certain page length, you may want to run a formatting macro only while the page count is below the maximum permitted length.
For help creating these additional types of loops, as well as other code structures discussed in this article, see Looping through code.
So, the code that we have reviewed to this point can definitely save you time. But, since you're formatting real documents in the real world, chances are that the formatting you need is not quite as straightforward as the examples used to this point. For example, what if the formatting shown earlier only applies to the text tables in your document and the financial tables require something else entirely?
Two types of conditional statements are available to help simplify this type of complication: If…Then…Else statements and Select Case statements.
To use a conditional statement, you just need to identify a condition that is true for all items that require the same formatting.
Perhaps all of your text tables are four columns or less and all financial tables are greater than five columns. In that situation, try an If…Then…Else statement.
Sub FormatTablesIf() Dim oTb As Table For Each oTb In ActiveDocument.Tables If oTb.Columns.Count < 5 Then oTb.Style = "Light Shading - Accent 4" oTb.Rows(1).Range.Style = ActiveDocument.Styles("Heading 2") oTb.AutoFitBehavior (wdAutoFitFixed) oTb.Rows.Alignment = wdAlignRowCenter oTb.Columns.PreferredWidth = InchesToPoints(0.6) Else oTb.Style = "Medium List 2 - Accent 4" oTb.Rows(1).Range.Style = ActiveDocument.Styles("Heading 4") End If Next oTb End Sub
The If…Then…Else structure is quite flexible. You can leave off the Else portion of the statement to only act if a single condition is true and do nothing otherwise. You can also add additional conditions by including an ElseIf statement within the structure for each subsequent condition. See Looping through code for examples of these variations on the If…Then…Else structure.
If you need to apply the same actions in multiple cases, use the And or Or operators as appropriate to include the additional cases in the same statement. For example, the first line of this If statement could have been the following:
To include multiple dependent arguments in a single evaluation statement, use parentheses to group arguments as you would in a mathematical expression.
Or, what if table styles are already used on the tables in your document and you need to apply different formatting based on the existing table style? Give SelectCase a try.
Sub FormatTablesSelect() Dim oTb As Table For Each oTb In ActiveDocument.Tables Select Case oTb.Style Case "Light Shading - Accent 4" oTb.AutoFitBehavior (wdAutoFitFixed) oTb.Rows.Alignment = wdAlignRowCenter oTb.Columns.PreferredWidth = InchesToPoints(0.6) Case "Medium List 2 - Accent 4" oTb.AutoFitBehavior (wdAutoFitWindow) oTb.Rows.Alignment = wdAlignRowLeft Case "Table Grid", "Table Normal" oTb.Style = "Light Grid - Accent 4" Case Else oTb.Style = "Medium List 1 - Accent 4" End Select Next oTb End Sub
As you see in this example, Select Case can be more efficient than an If…Then…Else structure where either structure type works. Notice that the condition is indicated in the Select Case statement and does not need to be repeated in each individual case. Also notice (as shown in the third Case statement in the previous example) that you can group multiple cases (when they require the same actions) by simply separating them with a comma.
As with the Else statement in an If…Then…Else structure, the Case Else statement in a Select Case structure is the default action (that is, it is used to apply actions to all instances other than where a specified condition is met). In this example, if you do not want to take action on any tables unless they use one of the styles specified in a Case statement in this structure, simply omit the Case Else statement.
In the sample macros used to demonstrate loops and conditions in this article, notice that the object variable is repeated for each line of code that performs and action on the specified object. But, now that you've grown accustomed to the idea that VBA can save you from repetitive tasks, doesn't that seem like too much work?
In fact, while writing VBA code, you rarely have to type the same thing twice. So, instead of typing that variable on each line, use a With statement to group all of the actions that you want to take on the same object. Take a look at the first version of the edited macro shown earlier after adding a With statement:
Sub FormatMyTables() Dim oTb As Table For Each oTb In ActiveDocument.Tables With oTb .Style = "Light Shading - Accent 4" .Rows(1).Range.Style = ActiveDocument.Styles("Heading 2") .AutoFitBehavior (wdAutoFitFixed) .Rows.Alignment = wdAlignRowCenter .Columns.PreferredWidth = InchesToPoints(0.6) End With Next oTb End Sub
When examining the preceding macro, notice the following:
The With statement is paired with a required End With statement that indicates the end of the grouping.
The With … End With structure is nested within the loop. When you nest statements in your macros, such as using a With statement or a conditional statement (or both) within a loop, pay careful attention to the nesting order. If, for example, you placed End With after Next oTb, the macro would not run.
In such a small amount of code as shown here, you may not see much savings in using a With statement. However, when you begin writing your own macros and writing longer macros, grouping statements can save a fair amount of time and typing. Using grouping statements is also an important best practice to learn if you may ever write more complex code because it can help your code run much more efficiently. That is, when you reference the object just once for a set of actions, the program can access that object just once to execute those actions rather than having to access the object separately to execute each line of code.
Okay, so this article has shown you how to write VBA macros for formatting lots of tables in a Word document, right? Wrong. This article has shown you a few core VBA concepts that you can use on whatever type of content you have in whatever type of document you need to format in the 2007 Office system including Excel, PowerPoint, or any other client application that uses VBA.
In VBA, the objects (such as a table or a paragraph) and what you can do to those objects are specific to the program in which you're working. But, many of the concepts—such as the loops, conditional statements, and grouping statements addressed here—are underlying VBA concepts that work the same across whatever program you're working in.
For example, suppose that you are working on a long PowerPoint 2007 presentation with several team members and each team member has used shapes to draw diagrams, but they each used different types of shapes and different formatting?
The following sample macro combines several of the concepts demonstrated in this article to change and format those shapes for consistency. Notice how multiple loops, conditional statements, and even a grouping statement are nested in this structure so that, with just a handful of code, you can clean up dozens of slides:
Sub FixUpShapes() Dim oSd As Slide Dim oSp As Shape For Each oSd In ActivePresentation.Slides For Each oSp In oSd.Shapes With oSp If oSp.Type = msoAutoShape Then If .AutoShapeType <> msoShapeDiamond Then .AutoShapeType = msoShapeRoundedRectangle .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2 .Line.ForeColor.ObjectThemeColor = msoThemeColorAccent1 Else .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1 .Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2 End If End If End With Next oSp Next oSd End Sub
Of course, that PowerPoint example might also help you remember what else you knew about programs in the 2007 Office system before you began using VBA. For example, writing this macro saves a tremendous amount of work if your presentation contains several dozen slides that use different shapes. But, what if only two slides in the presentation need to be fixed? VBA is one of my favorite tools, but in that case, I'd use the Change Shape feature and Shape Quick Styles in PowerPoint to take care of the job in a few clicks. (I might also show my team how to use SmartArt graphics instead of shapes for our next presentation, but that's for another article.)
Stephanie Krieger is a Microsoft Office System MVP and the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out andMicrosoft Office Document Designer. As a professional document consultant, Stephanie helps many global companies develop enterprise solutions for Microsoft Office on both platforms. She also frequently writes, presents, and creates content for Microsoft. You can reach Stephanie through her blog, arouet.net.