Price calculations in Word

[Dustin I]

I need to calculate prices in a table in word, and my VBA
needs to be able to start at the first row, look at aCell
and multiply it by bCell and place the value of that in
cCell. My problem is that I don't know how to reference
each cell location. I have done quite a bit of work with
excel/vba but nothing with word.

 

[Peter Hewett]

Hi Dustin

If your table is of a fixed size you don't need to use VBA (unless you
require it for other purposes) you can use Fields. Check Words (not the VBA
IDE) online help for working with Formula fields. This makes it relatively
easy (but tough compared to Excel!) to perform calcualtions by refering to
cells using an A1 (A=column, 1=row) reference.

This may also have relevance:
http://word.mvps.org/FAQs/TblsFldsFms/index.htm
http://word.mvps.org/FAQs/TblsFldsFms/TotalColumn.htm

HTH + Cheers - Peter

 

[macropod]

Hi Dustin,

A potentially VBA-free solution:
Say you need a formula on every row to multiply the contents of ColumnA by
the contents of ColumnB, then add the contents of ColumnC, and your formula
starts on Row1 of the table. To do that you could use a compound field like:

{QUOTE
{Set CellA "a{={SEQ RowNr}/2}"}
{Set CellB "b{={SEQ RowNr \c}/2}"}
{Set CellC "c{={SEQ RowNr \c}/2}"}
{={CellA}*{CellB}+{CellC} \# 0;-0}
}
where the braces '{}' are entered in pairs via Ctrl-F9. I've laid the field
out this way for readability - you can dispense with the internal CRs.

This field works by creating a sequence number of each row and incorporating
that plus the required column letters into bookmarks (CellA, CellB, CellC)
for those rows. These then become the cell addresses referenced in the
formula. You'll notice that the SEQ field has a the \c switch for the CellB,
CellC references, but not for the CellA reference. This is to stop multiple
SEQ references on the same row changing the SEQ No. (and hence the source
row number). You'll also notice that each bookmark includes a '/2' to divide
the SEQ No by 2. That's needed because of a flaw in the way Word updates SEQ
fields when used directly in a cell reference.

If your data doesn't start on the first row in the table, you need to add an
offset to the formula for each row before the first data row. So, if your
data starts on the second row, you'd put +1 after each '/2' expression (i.e.
{SEQ RowNr}/2+1, etc). If the data starts on the third row, you use +2, and
so on. Use the same technique to offset the cell referencing by a
predetermined number of rows, using -ve values to refer to rows above, and
+ve values to refer to rows below.

Relative referencing does not work for columns.

If you need to deal with the fact that new rows are being added, then all
you'l need is for you macro to copy the contents of another cell in the same
column then execute the command 'ActiveDocument.Fields.Update'.

Cheers