Faster Calculation Needed

[jasonbarker]

I'm having a speed issue that I think could be eliminated with a little
more knowledge.

I am creating a template form in Microsoft Word with 28 dollar amounts.
Each dollar amount is split into two text form fields (one for the
dollar portion of the amount and the other for the cents portion of the
dollar amount). I have set the name of each field (DollarAmt_01 to
DollarAmt_28 and CentsAmt_01 to CentsAmt_28). I have also created a
macro that gets run when the focus exits the text form field for both
the dollar and cents fields. This macro cycles through each dollar
amount adding them up. Then it cycles through the cents amount, adding
them up, multiplying them by 0.01 and adding the result to the dollar
amount total.

Here's a snippet of my code for the macro:
Sub CalcDepositTicketTotals()
Dim dollarTotal, centsTotal As Double

dollarTotal = 0
dollarTotal = dollarTotal +
Val(ActiveDocument.FormFields("DollarAmt_01").Result)
dollarTotal = dollarTotal +
Val(ActiveDocument.FormFields("DollarAmt_02").Result)
...
dollarTotal = dollarTotal +
Val(ActiveDocument.FormFields("DollarAmt_28").Result)

centsTotal = 0
centsTotal = centsTotal +
Val(ActiveDocument.FormFields("CentsAmt_01").Result)
centsTotal = centsTotal +
Val(ActiveDocument.FormFields("CentsAmt_02").Result)
...
centsTotal = centsTotal +
Val(ActiveDocument.FormFields("CentsAmt_28").Result)

centsTotal = centsTotal * 0.01
dollarTotal = dollarTotal + centsTotal

' US Dollar Amount
ActiveDocument.FormFields("USD_Total").Result = Str(dollarTotal)

End Sub
=====================

I'm sure that I am going about this the wrong way because this is
taking way too long to process (about 16-18 seconds). Certainly, you
can tell that I am not experienced with programming in Word. Any
suggestions on what I can do to speed things up?

Thanks!
Jason

 

[Jonathan West]

Hi jason,

The most common cause of slow performance in Word VBA is having to make too
many accesses to the Word object model. Since you don't have a loop that is
calculating a very large number of times, object use is the area I would
concentrate on.

There are three possible approaches which might help.

1. Reduce the dots.
===================
The expression

ActiveDocument.FormFields("DollarAmt_01").Result

is shorthand for this

ActiveDocument.FormFields.Item("DollarAmt_01").Result

There are 3 dots in that. Since you have 56 formfields you are reading, that
is 168 dots you are using. Each dot is a drilldown within the object model,
and it takes time to do it. Therefore you can speed your code by reducing
the number of dots. There are two equally good approaches to this - they
both work in a very similar way under the hood, so choosing between them is
largely a matter of coding style.

The first way is wo use a With/End With statement pair, like this

With ActiveDocument.FormFields
dollarTotal = 0
dollarTotal = dollarTotal + Val(.Item("DollarAmt_01").Result)
dollarTotal = dollarTotal + Val(.Item("DollarAmt_02").Result)
..
dollarTotal = dollarTotal + Val(.Item("DollarAmt_28").Result)

centsTotal = 0
centsTotal = centsTotal + Val(.Item("CentsAmt_01").Result)
centsTotal = centsTotal + Val(.Item("CentsAmt_02").Result)
...
centsTotal = centsTotal + Val(.Item("CentsAmt_28").Result)

centsTotal = centsTotal * 0.01
dollarTotal = dollarTotal + centsTotal

' US Dollar Amount
.Item("USD_Total").Result = Str(dollarTotal)
End With

This way, you now have just two dots per item instead of 3, and this may
make a noticeable difference.

The second way o doing this is to use an object variable, like this

Dim oFF as FormFields
Set oFF = ActiveDocument.FormFields
dollarTotal = 0
dollarTotal = dollarTotal + Val(oFF.Item("DollarAmt_01").Result)
dollarTotal = dollarTotal + Val(oFF.Item("DollarAmt_02").Result)
...
dollarTotal = dollarTotal + Val(oFF.Item("DollarAmt_28").Result)

centsTotal = 0
centsTotal = centsTotal + Val(oFF.Item("CentsAmt_01").Result)
centsTotal = centsTotal + Val(oFF.Item("CentsAmt_02").Result)
...
centsTotal = centsTotal + Val(oFF.Item("CentsAmt_28").Result)
centsTotal = centsTotal * 0.01
dollarTotal = dollarTotal + centsTotal

' US Dollar Amount
..Item("USD_Total").Result = Str(dollarTotal)



There is usually not much to choose between these two methods in terms iof
performance, so which on you use is a matter of coding style. Personally, I
tend to use With/Wnd With more often, and use object variables only when I
need to drill down in two separate places at the same time to pass
information between them.

2. Reduce the formfields
========================
I suggest you combine the dollar and cent components of each amount into a
single formfield. You can set the properties of a formfield so that only a
number in the form "0.00" can be entered. That way, you are sure to get a
valid value entered. This will halve the number of formfields you have to
access, and therefore double the speed of cour code, since reading the
formfields is almost certainly the most time-consuming part of the code.

3. Redo the whole thing in Excel
================================
If you need regular rapid calculation of this kind of form, then you are
much better off doing all this in Excel, where the total can be nearly
instananeously recalculated whenever any item is entered. You can also
protect the cells of an Excel worksheet so that user can enter values only
in the equivalents of the formfields.


--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

 

[Greg Maxey]

Jason,

You know more about programming than I do and I am sure someone will be
along to help with your macro. A observation if I may. If you are using a
form with text form fields, why don't you simply use a calculation form
field to perform the calculation for you? You don't need a macro to do
this.

You can use a single text field to hold both dollars and cents. Make sure
each one is set to calculate on exit. If they are aligned in a column then
the calculation fields is =sum(above). If they are scattered about you
woul need to list individually (i.e., =sum(text1,text2,...text28). With
text1, text2, etc. being the bookmark name of the field.

 

[Lars-Eric Gisslén]

Jason.

Skip the 'Activedocument.FormFields()' syntax. It's way to slow if you have
to write to/read many formfields during your calculations. You should use
the WordBasic syntax instead like:

nVal = Val(WordBasic.[getformresult$]("FormFieldName"))
WordBasic.[setformresult] "ResultFormField", Str(nCalculation)

It's blistering fast compared to 'Activedocument.FormFields()'. It's even
faster than using calculated FormFields (FormFields with formulas).