{"id":55423,"date":"2008-06-04T04:30:00","date_gmt":"2008-06-04T04:30:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/06\/04\/hey-scripting-guy-how-can-i-determine-the-largest-value-in-a-table-column-in-microsoft-office-word\/"},"modified":"2008-06-04T04:30:00","modified_gmt":"2008-06-04T04:30:00","slug":"hey-scripting-guy-how-can-i-determine-the-largest-value-in-a-table-column-in-microsoft-office-word","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-determine-the-largest-value-in-a-table-column-in-microsoft-office-word\/","title":{"rendered":"Hey, Scripting Guy! How Can I Determine the Largest Value in a Table Column in Microsoft Office Word?"},"content":{"rendered":"

\"Hey, <\/h2>\n

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\u2019s the easiest way to determine the largest value in each column in a table?
— GL<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, GL. Great question. Here’s the script: <\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. 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\u2019t been quite as useful.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At any rate, over the past few weeks we\u2019ve been dealing with all sorts of \u2026 suggestions \u2026 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<\/i> leave you in a bad mood.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. OK, true: for Greg that doesn\u2019t make much difference; he\u2019s always in a bad mood anyway. But not Jean; good old Jean is always smiling and laughing and enjoying life.<\/p>\n

But, then again, that was the old Jean. The new Jean is different.<\/p>\n

Actually, come to think of it, even the new Jean is<\/i> pretty old, isn\u2019t she?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

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\u2019t<\/i> change no matter what? It\u2019s 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.<\/p>\n

Oh, wait; check that. That almost assuredly will<\/i> change; we probably won\u2019t 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\u2019ve already written one:<\/p>\n

Const NUMBER_OF_ROWS = 1\nConst NUMBER_OF_COLUMNS = 3\n\nSet objWord = CreateObject(\"Word.Application\")\nobjWord.Visible = True\n\nSet objDoc = objWord.Documents.Add()\nSet objRange = objDoc.Range()\n\nobjDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS\nSet objTable = objDoc.Tables(1)\n\nobjTable.Cell(1, 1).Range.Text = \"Month\"\nobjTable.Cell(1, 2).Range.Text = \"Ken Myer\"\nobjTable.Cell(1, 3).Range.Text = \"Pilar Ackerman\"\n\nobjTable.Rows.Add()\nobjTable.Cell(2, 1).Range.Text = \"January\"\nobjTable.Cell(2, 2).Range.Text = \"422\"\nobjTable.Cell(2, 3).Range.Text = \"478\"\n\nobjTable.Rows.Add()\nobjTable.Cell(3, 1).Range.Text = \"February\"\nobjTable.Cell(3, 2).Range.Text = \"475\"\nobjTable.Cell(3, 3).Range.Text = \"391\"\n\nobjTable.Rows.Add()\nobjTable.Cell(4, 1).Range.Text = \"March\"\nobjTable.Cell(4, 2).Range.Text = \"388\"\nobjTable.Cell(4, 3).Range.Text = \"314\"\n\nobjTable.Rows.Add()\nobjTable.Cell(5, 1).Range.Text = \"Maximum Value\"\nobjTable.Cell(5, 2).Formula \"=Max(ABOVE)\"\nobjTable.Cell(5, 3).Formula \"=Max(ABOVE)\"\n\nobjTable.Rows.Add()\nobjTable.Cell(6, 1).Range.Text = \"Overall Maximum\"\nobjTable.Cell(6, 2).Formula \"=Max(B2:C5)\"\nobjTable.Cell(6, 3).Range.Text = \"\"\n<\/pre>\n
To begin with, don\u2019t 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<\/i> the columns. (That is, the overall highest value.) That\u2019s extra code that we didn\u2019t need in order to answer GL\u2019s question, but we tacked it onto the end just to show you how to perform that kind of calculation.<\/p>\n
And yes, now that you mention it, it was a little dachshund from North Dakota who suggested we do that.<\/p>\n
But we\u2019ll deal with that later. For now, the script kicks off by defining a pair of constants \u2013 NUMBER_OF_ROWS and NUMBER_OF_COLUMNS; we\u2019ll use these two constants to define the starting size of our table (1 row by 3 columns). And don\u2019t worry; like we said, that\u2019s just the starting size. By the time we\u2019re done our table will have more than just a single row. <\/p>\n
We promise.<\/p>\n
After defining our constants we create an instance of the Word.Application<\/b> object and then set the Visible<\/b> 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\u2019s Range<\/b> object:<\/p>\n
Set objDoc = objWord.Documents.Add()\nSet objRange = objDoc.Range()\n<\/pre>\n
Why do we need the Range object? That\u2019s easy. (Or at least the Golden Retriever says it\u2019s 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\u2019s because we created our range object at the very beginning of the document.<\/p>\n
Speaking of which, in our next line of code we do the very thing we were just talking about: we call the Add<\/b> method (a method that belongs to the Tables<\/b> collection) and add a new table to the document:<\/p>\n
objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS\n<\/pre>\n
As you can see, this isn\u2019t particularly complicated: we simply call the Add method followed by the table location (again, we\u2019re 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\u2019ve 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):<\/p>\n
Set objTable = objDoc.Tables(1)\n<\/pre>\n
Of course, there\u2019s not much point to having a snazzy new table if we don\u2019t actually do<\/i> something with that table, is there? OK, sure, we suppose we could<\/i> leave it as-is in the hope that it might someday become a collector\u2019s item. But we decided it would be more fun to remove the shrink-wrap and fill in the cells in row 1 instead:<\/p>\n
objTable.Cell(1, 1).Range.Text = \"Month\"\nobjTable.Cell(1, 2).Range.Text = \"Ken Myer\"\nobjTable.Cell(1, 3).Range.Text = \"Pilar Ackerman\"\n<\/pre>\n
Needless to say, this is all pretty straightforward. In line 1 we\u2019re simply assigning the value Month<\/i> to the first cell in the table (cell row 1, column 1); in line 2, we\u2019re assigning the value Ken Myer<\/i> to the second cell in the table (row 1, column 2). And in line 3 \u2013 well, you get the idea.<\/p>\n
And what happens after we assign values to all the cells in row 1? Why, then we simply call the Add<\/b> method of the Rows<\/b> collection to add a new row to the table:<\/p>\n
objTable.Rows.Add()\n<\/pre>\n
From there we assign values to all the cells in row 2:<\/p>\n
objTable.Cell(2, 1).Range.Text = \"January\"\nobjTable.Cell(2, 2).Range.Text = \"422\"\nobjTable.Cell(2, 3).Range.Text = \"478\"\n<\/pre>\n
This process continues until we\u2019ve added all the data to the table. At this point, our table will look something like this:<\/p>\n
\"Microsoft <\/p>\n
This means we\u2019ve 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.) <\/p>\n
So how hard is the hard part going to be? As it turns out, not that hard at all:<\/p>\n
objTable.Rows.Add()\nobjTable.Cell(5, 1).Range.Text = \"Maximum Value\"\nobjTable.Cell(5, 2).Formula \"=Max(ABOVE)\"\nobjTable.Cell(5, 3).Formula \"=Max(ABOVE)\"\n<\/pre>\n
Again, there\u2019s nothing fancy going on here. In line 1 we\u2019re simply adding a new row to the table; in line 2, we\u2019re assigning a label (Maximum Value<\/i>) to the first cell in this new row. And what about line 3:<\/p>\n
objTable.Cell(5, 2).Formula \"=Max(ABOVE)\"\n<\/pre>\n
What we\u2019re doing here is using the Formula<\/b> method to assign a formula to the middle cell in row 5. More specifically, we\u2019re assigning this<\/i> formula to the middle cell in row 5:<\/p>\n
=Max(ABOVE)\n<\/pre>\n
In other words, we\u2019re using the Max<\/b> function to determine the Maximum number in a specified range. And what is<\/i> that specified range? In this case, it\u2019s 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\u2019s 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:<\/p>\n
objTable.Cell(5, 3).Formula \"=Max(ABOVE)\"\n<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Good question: are<\/i> 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<\/b> menu and then click Field<\/b>. In the Field<\/b> dialog box, click Formula<\/b>. In the Formula<\/b> dialog box, click the dropdown list labeled Paste function<\/b>. The items in the dropdown list are the functions available to you.<\/p>\n
In Word 2007, select Equation<\/b> from the Insert<\/b> tab.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
And then, just for the heck of it, we added this block of code to the end of the script:<\/p>\n
objTable.Rows.Add()\nobjTable.Cell(6, 1).Range.Text = \"Overall Maximum\"\nobjTable.Cell(6, 2).Formula \"=Max(B2:C5)\"\nobjTable.Cell(6, 3).Range.Text = \"\"\n<\/pre>\n
What we\u2019ve 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:<\/p>\n
\"=Max(B2:C5)\"\n<\/pre>\n
Once again we\u2019re using the Max formula, only this time we aren\u2019t specifying all the cells in the column; instead we\u2019re specifying the range B2 to C5. As you probably guessed that range just happens to contain all the data in our table.<\/p>\n
So what will our table look like now? Well, with any luck (something that\u2019s been in short supply around Scripting Guys Headquarters lately) our table should look like this:<\/p>\n
\"Microsoft <\/p>\n
Oh, and here\u2019s 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\u2019s fine; just use this line of code:<\/p>\n
objTable.Cell(6, 2).Formula \"=Max(B2:C5)\", \"#.00\"\n<\/pre>\n
Give that a try and see what happens.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s the easiest way to determine the largest value in each column in a […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[84,49,3,5],"class_list":["post-55423","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-word","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s the easiest way to determine the largest value in each column in a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55423","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55423"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55423\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}