Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Inserting Formulas in a Microsoft Word Table

Here’s a bit of trivia for you: Paul McCartney (yes, that Paul McCartney) has a brother – Peter Michael McCartney – who used to be a rock and roller, too. In fact, under the name of Mike McGear, he and the Scaffolds actually hit #1 on the British pop charts with the song Lily the Pink. You say you haven’t heard of either Mike McGear or Lily the Pink? Well, let’s face it: when you’re big brother is Paul McCartney it’s hard to get people to pay any attention to your music.

In a way, the mathematical capabilities built into Microsoft Word are like the Mike McGear of the software industry. Word actually has the ability to do such things as add numbers and calculate averages. But, let’s face it: when your big brother is Microsoft Excel it’s hard to get people to pay any attention to your mathematical capabilities.

Nevertheless, if you’re going to use Word to create automated reports you might as well take advantage of Word’s arithmetic skills, right? For example, suppose you generate a table that looks like this:

You might want to add another row to this table, one that adds up the total number of employees, or maybe one that determines the average number of employees per department. You could add additional VBScript code to your script that will tally up this information; you could even shell out to Excel and ask Excel to figure this out for you. Or, you could simply add one line of code to your script and have Word do these calculations. And that’s what we’re going to talk about today.

More specifically, we’re going to focus on adding formulas to a table. There’s two reasons for that: first, more often than not that’s where you’ll want to place a formula anyway, and second, that way this column can serve as a follow-up to last week’s article on creating tables in Microsoft Word. (Which is also a sneaky way for us to not have to explain each and every line in the script; we won’t talk about the code for creating tables, only the code for inserting formulas.)

Let’s take a look at a script that creates the preceding table (and, just for the heck of it, formats it as well):

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 2

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()

Set objRange = objDoc.Range()

objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

Set objTable = objDoc.Tables(1)

objTable.Cell(1, 1).Range.Text = "Department"
objTable.Cell(1, 2).Range.Text = "Number of Employees"
objTable.Rows.Add()

objTable.Cell(2, 1).Range.Text = "Administration"
objTable.Cell(2, 2).Range.Text = "211"
objTable.Rows.Add()

objTable.Cell(3, 1).Range.Text = "Finance"
objTable.Cell(3, 2).Range.Text = "105"
objTable.Rows.Add()

objTable.Cell(4, 1).Range.Text = "Human Resources"
objTable.Cell(4, 2).Range.Text = "45"
objTable.Rows.Add()

objTable.Cell(5, 1).Range.Text = "Manufacturing"
objTable.Cell(5, 2).Range.Text = "673"
objTable.Rows.Add()

objTable.Cell(6, 1).Range.Text = "Research"
objTable.Cell(6, 2).Range.Text = "38"
objTable.Rows.Add()

objTable.Cell(7, 1).Range.Text = "Sales"
objTable.Cell(7, 2).Range.Text = "93"
objTable.Rows.Add()

objTable.Cell(8, 1).Range.Text = "Total"
objTable.Cell(8, 2).AutoSum

objTable.AutoFormat(5)

Like we said, we won’t discuss the part of the code that actually creates the table in any detail. As a quick overview, though, we begin by creating an instance of the Word.Application object and then set the Visible property to True. We create a new document, create an instance of the Range object, then use the Add method to add a new table to the Tables collection. All of that is explained in last week’s column.

We then start adding information to the table. After entering all the data, we use this line of code to add a blank row at the bottom of the table:

objTable.Rows.Add()

And then we use these two lines of code to fill out that bottom row:

objTable.Cell(8, 1).Range.Text = "Total"
objTable.Cell(8, 2).AutoSum

The first line is self-explanatory: it simply types the word Total into the first cell in row 8.

The line we really care about is the one that calls the AutoSum method. This method inserts a formula into the second cell in row 8. We can use the AutoSum method because of the way we have constructed our table. When you call the AutoSum method, Word checks to see if the cell directly above the cell with the formula (in this case, row 7, column 2) has a value. If it does, then Word automatically adds up all the cells in that column (that is, all the values above the formula). If the cell above the formula does not contain a value but the cell to the immediate left of the formula does, then Word automatically adds up all the values in the row. Make sense? And if neither the cell above or to the left has a value, then Word reverts back to adding up all the values in the column.

Yes, we know you’re confused, so let’s show you an example or two. (In each example, we’ll use AutoSum to indicate the cell where the AutoSum method is being called.) Here’s our first example:

Which two numbers will be autosummed? That’s easy: 224 and 445. That’s because the cell directly above the AutoSum formula has a value. Thus we add up the numbers in the column.

Here’s another example:

OK, this is even easier, but it illustrates the point. In this case, Word will add the numbers 67 and 23. Why? Because there is no value in the cell directly above the formula, but there is a value in the cell directly to the left. Thus Word adds up the values in the row.

Does that help? Good.

If you still find this a little confusing, though, don’t worry about it: instead of using AutoSum you can use the Sum formula and specify whether you want to add the numbers in the column (ABOVE) or the numbers in the row (LEFT). For example, this code instructs Word to add the numbers in the column:

objTable.Cell(8, 2).Sum(ABOVE)

Pretty easy, huh? In our sample table it wouldn’t really make any sense to add the numbers in each row, but if that’s what you wanted to do you would use this code:

objTable.Cell(8, 2).SUM(LEFT)

By the way, this is also how you call Word’s other mathematical functions, things like Average, Min, and Max. Do you want to know the average number of employees per department? Here’s the code:

objTable.Cell(8, 2).Formula "=Average(ABOVE)"

objTable.Cell(8, 2).Formula "=Average(ABOVE) \# " & chr(34) & "0" & chr(34) & ""

You can also indicate a specific range of cells to be included in the formula. For example, suppose you only wanted to add up the number of employees in the Administration, Finance, and Human Resources department. Those three values are found in cells B2, B3, and B4. (And, yes, now we’re using Excel-like syntax to refer to individual cells in the table. Why? That’s just the way it is.) Here’s the code that adds up the values in just those three cells:

objTable.Cell(8, 2).Formula "=Sum(B2:B4)"

Got all that? If we go back now and run our original script (the one that called the AutoSum method) we should end up with a table that looks like this:

In case you’re wondering, we used a single line of code to add the formatting to the table:

objTable.AutoFormat(5)

If you’d like more information about the AutoFormat method, check out last week’s column. (Yes, we really are pushing last week’s column, aren’t we? Maybe we should have a clearance sale or something.)