Hey, Scripting Guy! How Can I Determine the Largest Value in a Table Column in Microsoft Office Word?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! I have a script that automatically creates a Microsoft Office Word table that features several columns of data. At the bottom of each column I need to list the largest number that can be found in that column. What’s the easiest way to determine the largest value in each column in a table?
— GL

SpacerHey, Scripting Guy! AnswerScript Center

Hey, GL. Great question. Here’s the script:

Note. In all fairness, we have to admit that the dogs have actually come up with some pretty good ideas; in fact, the Scripting Games are based on a suggestion by a Pekinese from the U District in Seattle. Unfortunately, though, the ideas that the people have come up with haven’t been quite as useful.

At any rate, over the past few weeks we’ve been dealing with all sorts of … suggestions … and while we have no doubt that the Script Center, and the Scripting Guys, will emerge unscathed (we always do), well, still, the whole process can leave you in a bad mood.

Note. OK, true: for Greg that doesn’t make much difference; he’s always in a bad mood anyway. But not Jean; good old Jean is always smiling and laughing and enjoying life.

But, then again, that was the old Jean. The new Jean is different.

Actually, come to think of it, even the new Jean is pretty old, isn’t she?

At any rate, sometime in the future there could be a few changes to the Script Center; whether those changes will be for the better remains to be seen. But you know one thing that won’t change no matter what? It’s this: come hell or high water, the Scripting Guys will continue to write scripts that can pick out the largest value in a column of numbers in a Microsoft Word table.

Oh, wait; check that. That almost assuredly will change; we probably won’t continue to write scripts that can pick out the largest value in a column of numbers in a Microsoft Word table. Why not? Because we’ve already written one:

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 3

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 = "Month"
objTable.Cell(1, 2).Range.Text = "Ken Myer"
objTable.Cell(1, 3).Range.Text = "Pilar Ackerman"

objTable.Rows.Add()
objTable.Cell(2, 1).Range.Text = "January"
objTable.Cell(2, 2).Range.Text = "422"
objTable.Cell(2, 3).Range.Text = "478"

objTable.Rows.Add()
objTable.Cell(3, 1).Range.Text = "February"
objTable.Cell(3, 2).Range.Text = "475"
objTable.Cell(3, 3).Range.Text = "391"

objTable.Rows.Add()
objTable.Cell(4, 1).Range.Text = "March"
objTable.Cell(4, 2).Range.Text = "388"
objTable.Cell(4, 3).Range.Text = "314"

objTable.Rows.Add()
objTable.Cell(5, 1).Range.Text = "Maximum Value"
objTable.Cell(5, 2).Formula "=Max(ABOVE)"
objTable.Cell(5, 3).Formula "=Max(ABOVE)"

objTable.Rows.Add()
objTable.Cell(6, 1).Range.Text = "Overall Maximum"
objTable.Cell(6, 2).Formula "=Max(B2:C5)"
objTable.Cell(6, 3).Range.Text = ""

To begin with, don’t be dismayed by the amount of code shown here: most of that code is simply used for adding values to the different cells in our table. On top of that, we made this table a tiny bit fancier than we needed to: in addition to showing you the Maximum value in each column we also show you the Maximum value over all the columns. (That is, the overall highest value.) That’s extra code that we didn’t need in order to answer GL’s question, but we tacked it onto the end just to show you how to perform that kind of calculation.

And yes, now that you mention it, it was a little dachshund from North Dakota who suggested we do that.

But we’ll deal with that later. For now, the script kicks off by defining a pair of constants – NUMBER_OF_ROWS and NUMBER_OF_COLUMNS; we’ll use these two constants to define the starting size of our table (1 row by 3 columns). And don’t worry; like we said, that’s just the starting size. By the time we’re done our table will have more than just a single row.

We promise.

After defining our constants we create an instance of the Word.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Word that we can see onscreen. We then use these two lines of code to create a new Word document, and to create an instance of the document’s Range object:

Set objDoc = objWord.Documents.Add()
Set objRange = objDoc.Range()

Why do we need the Range object? That’s easy. (Or at least the Golden Retriever says it’s easy.) In order to create a new table, we need to specify the location of that table; the simplest way to do that is to create a Range object and then pass that object to the method that adds the table. In this case, our table will appear at the very beginning of the document; that’s because we created our range object at the very beginning of the document.

Speaking of which, in our next line of code we do the very thing we were just talking about: we call the Add method (a method that belongs to the Tables collection) and add a new table to the document:

objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

As you can see, this isn’t particularly complicated: we simply call the Add method followed by the table location (again, we’re using the object reference objRange to indicate the table location); the number of rows in the table; and the number of columns in the table. After we’ve done that we use the following line of code to create an object reference to the new table (which, needless to say, happens to be table 1 in the Tables collection):

Set objTable = objDoc.Tables(1)

Of course, there’s not much point to having a snazzy new table if we don’t actually do something with that table, is there? OK, sure, we suppose we could leave it as-is in the hope that it might someday become a collector’s item. But we decided it would be more fun to remove the shrink-wrap and fill in the cells in row 1 instead:

objTable.Cell(1, 1).Range.Text = "Month"
objTable.Cell(1, 2).Range.Text = "Ken Myer"
objTable.Cell(1, 3).Range.Text = "Pilar Ackerman"

Needless to say, this is all pretty straightforward. In line 1 we’re simply assigning the value Month to the first cell in the table (cell row 1, column 1); in line 2, we’re assigning the value Ken Myer to the second cell in the table (row 1, column 2). And in line 3 – well, you get the idea.

And what happens after we assign values to all the cells in row 1? Why, then we simply call the Add method of the Rows collection to add a new row to the table:

objTable.Rows.Add()

From there we assign values to all the cells in row 2:

objTable.Cell(2, 1).Range.Text = "January"
objTable.Cell(2, 2).Range.Text = "422"
objTable.Cell(2, 3).Range.Text = "478"

This process continues until we’ve added all the data to the table. At this point, our table will look something like this:

Microsoft Word

This means we’ve now reached the hard part of the task: we need to add a row that reports back the Maximum value found in each column in the table. (Well, OK, not the first column, which simply holds month names. But the other two columns.)

So how hard is the hard part going to be? As it turns out, not that hard at all:

objTable.Rows.Add()
objTable.Cell(5, 1).Range.Text = "Maximum Value"
objTable.Cell(5, 2).Formula "=Max(ABOVE)"
objTable.Cell(5, 3).Formula "=Max(ABOVE)"

Again, there’s nothing fancy going on here. In line 1 we’re simply adding a new row to the table; in line 2, we’re assigning a label (Maximum Value) to the first cell in this new row. And what about line 3:

objTable.Cell(5, 2).Formula "=Max(ABOVE)"

What we’re doing here is using the Formula method to assign a formula to the middle cell in row 5. More specifically, we’re assigning this formula to the middle cell in row 5:

=Max(ABOVE)

In other words, we’re using the Max function to determine the Maximum number in a specified range. And what is that specified range? In this case, it’s all the cells in the column above the cell with the formula; we know that because we used the keyword ABOVE when specifying the formula range. (If we were working with a row, we could put a formula in the cell at the end of the row and use the keyword LEFT to determine the Maximum number for all the cells in that row.) Believe it or not, that’s all we have to do to determine the largest number in column 2. We then use the exact same approach to determine the largest number in column 3:

objTable.Cell(5, 3).Formula "=Max(ABOVE)"

Note. Good question: are there other functions we can use besides Max? As a matter of fact there are. To see which functions are available, do this: from within Word, click on the Insert menu and then click Field. In the Field dialog box, click Formula. In the Formula dialog box, click the dropdown list labeled Paste function. The items in the dropdown list are the functions available to you.

In Word 2007, select Equation from the Insert tab.

And then, just for the heck of it, we added this block of code to the end of the script:

objTable.Rows.Add()
objTable.Cell(6, 1).Range.Text = "Overall Maximum"
objTable.Cell(6, 2).Formula "=Max(B2:C5)"
objTable.Cell(6, 3).Range.Text = ""

What we’ve done here is add another row to the table, this row designed to display the largest number found in the entire table. Notice the formula we use for that:

"=Max(B2:C5)"

Once again we’re using the Max formula, only this time we aren’t specifying all the cells in the column; instead we’re specifying the range B2 to C5. As you probably guessed that range just happens to contain all the data in our table.

So what will our table look like now? Well, with any luck (something that’s been in short supply around Scripting Guys Headquarters lately) our table should look like this:

Microsoft Word

Oh, and here’s a nifty little trick you might find useful. In addition to specifying the function to be assigned to a cell the Formula method also lets you specify an optional number format. For example, suppose you want to display the overall maximum value using two decimal places. That’s fine; just use this line of code:

objTable.Cell(6, 2).Formula "=Max(B2:C5)", "#.00"

Give that a try and see what happens.

0 comments

Discussion is closed.

Feedback usabilla icon