A reader recently asked for help writing a VBA program for the common task in every invoice: multiply price times quantity for line items. Pretty easy ... except that the reader wanted to do it all in a Word document.
Here's an example.
--------
Click Here to display the illustration
Click the Back button on your browser to return
--------
The reader complained that he was having some trouble making the program work. This reminds of an old joke:
Patient: Doctor, doctor! It hurts when I do this!
Doctor: Don't do that!
That's the first advice I would give here: "Don't do that!" Trying to use text in a Word document to do calculations is inherently difficult and will inevitably lead to errors and problems sooner or later. One solution is to embed a linked Excel spreadsheet into the Word document. This gives you all of the calculation ability of Excel combined with the word processing and formatting of Word. Another is to use Word VBA to input individual values in Office forms and then transfer them to the table in the document using VBA. Both of these ideas are a lot better than trying to process the text in a Word document directly.
But that's not what the reader asked for. So, in spite of the fact that it's not a good idea, here's a VBA program that will process the table shown above and update the rows and columns. Even if you don't plan to do this, it's a good example showing how tables can be accessed in Word VBA.
Dim RowCntr As Integer
RowCntr = 2
Dim Quantity As Integer
Dim UnitPrice As Currency
Dim LineTotal As Currency
Dim OneCell As Range
Dim GrandTotal as Currency
GrandTotal = 0
Set tbl = ActiveDocument.Tables(2)
For RowCntr = 2 To tbl.Rows.Count
Set OneCell = tbl.Cell(RowCntr, 4).Range
OneCell.MoveEnd Unit:=wdCharacter, Count:=-1
Quantity = Val(OneCell.Text)
Set OneCell = tbl.Cell(RowCntr, 5).Range
OneCell.MoveEnd Unit:=wdCharacter, Count:=-1
UnitPrice = Val(OneCell.Text)
LineTotal = Quantity * UnitPrice
tbl.Cell(RowCntr, 6).Range = LineTotal
GrandTotal = GrandTotal + LineTotal
Next 'rowCntr
ActiveDocument.Tables(3).Cell(1, 6).Range = GrandTotal
One of the first problems is that you can't change the document without changing the program to match. For example, the tables that contain the line items is the second one in this particular document. If someone updated the document to add or delete a table, the program wouldn't work anymore. You also can't add or delete a column without changing the program.
The program processes each row of the table and keeps a running total. It starts on row 2 to avoid processing the header row. But there's some interesting processing that's unrelated to actually calculating the totals.
Set OneCell = tbl.Cell(RowCntr, 4).Range
OneCell.MoveEnd Unit:=wdCharacter, Count:=-1
The individual cells in Word tables contain a character called an end-of-cell marker which holds formatting for the cell. If this character is extracted with the rest of the text in the cell, then the text can't be used for a a calculation and a runtime error is thrown. (If just ordinary text is found in the cell, the value is interpreted as 0 for calculations.) To use the cell contents in a calculation, the value in the cell without the end-of-cell marker has to be obtained. The Set keyword has to be used because this is still the old VB 6 technology. If you don't use the Set statement, the variable is set to the value in the range, rather than the Range object itself.
In addition to this code, you also have to make sure that security is set appropriately to allow the VBA to be executed and provide a way to do that. (Perhaps a button on the document saying, "Update the Lineitems".)
It's a fairly "brittle" solution, but you don't have to have anything but Word to run it.
