How can I calculate the sum of Legacy Form Fields

问题:

I have a Word document with a table that contains the price of an item, the order amount and the item sum, where amount and item sum are Legacy Form Fields:

There are other items available as options that you can chose via checkbox (also Legacy Form Fields):

Code

Now I've created a function that calculates the item sum

Sub order_amount_Click()
    Dim amount As Integer
    Dim valueFromCell As String
    Dim list
    Dim value As Double

    amount = ActiveDocument.FormFields("order_amount").Result
    If amount > 0 Then
        valueFromCell = ThisDocument.Tables(1).Cell(5, 3).Range.Text
        list = Split(valueFromCell, " ")
        value = list(0)
        subsum = value * amount
        ActiveDocument.FormFields("order_amount_sum").Result = subsum
    Else
        ActiveDocument.FormFields("order_amount_sum").Result = 0
    End If

    CalculateSum
End Sub

and another function that calculates the item sum for additional items (if the checkbox is activated) multiplied by the order amount:

Sub optional_item_Click()
    Dim checkbox As Integer
    Dim valueFromCell As String
    Dim list
    Dim value As Double
    Dim amount As Integer
    Dim subsum As Double

    checkbox = ActiveDocument.FormFields("optional_item1").Result
    amount = ActiveDocument.FormFields("order_amount").Result

    If checkbox = 1 And amount > 0 Then
        valueFromCell = ThisDocument.Tables(1).Cell(7, 3).Range.Text
        list = Split(valueFromCell, " ")
        value = list(0)
        subsum = value * amount
        ActiveDocument.FormFields("optional_item1_sum").Result = subsum
    Else
        ActiveDocument.FormFields("optional_item1_sum").Result = 0
    End If

    CalculateSum
End Sub

(There are as many Click() functions as there are optional_item fields, 17 in sum - I haven't generalized the function yet.)

The last line within both Subs is a function call, that calculates the net sum, the VAT and the final total sum

Function CalculateSum()
    Dim net As Double
    Dim vat As Double
    Dim total_sum As Double

    net = CDbl(ActiveDocument.FormFields("order_amount_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item1_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item2_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item3_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item4_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item5_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item6_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item7_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item8_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item9_sum").Result)

    ' Cannot compile when line too long, so splitting into two statements
    net = net + CDbl(ActiveDocument.FormFields("optional_item10_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item11_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item12_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item13_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item14_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item15_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item16_sum").Result) + CDbl(ActiveDocument.FormFields("optional_item17_sum").Result)
    'MsgBox "net " & net
    ActiveDocument.FormFields("net_sum").Result = net

    vat = net * 0.19
    'MsgBox "vat " & vat
    ActiveDocument.FormFields("vat_sum").Result = vat

    total_sum = net + vat
    'MsgBox "total " & total_sum
    ActiveDocument.FormFields("total_sum").Result = total_sum
End Function

Problem

The code itself works fine, all elements are calculated correctly. But there are two major problems that make the whole document almost unusable (all actions for users are restricted to enter their name on top of the document, chose the amount and de-/activate the checkboxes; sum fields aren't accessible):

1. When I perform an action (e.g. enter an amount) the CalculateSum() function visibly loops over all sum fields (spanned over three pages) and weirdly scrolls on the document along the sum fields.
2. When I click on a checkbox on the 2nd or 3rd page, the document scrolls up to the first page to the place where I can enter the amount of pieces I want to order.

Now how can I supress all that looping over the sum fields and weirdly scrolling around? And how can I prevent the document from scrolling up to the 1st page?

Any help appreciated! (I'm also thankful for side comments on my code, as I'm new to VBA.)

Update #1

I've added a screencast showing the error.

Update #2

The basic problem seems to be that the macro is scrolling to the position I am referring to in my script, e.g. if I'm using

ActiveDocument.FormFields("total_sum").Result = total_sum

it's scrolling to the total_sum field.

回答1:

Too difficult for a comment...

For (b) you have to use VBA - you can't use the trick I suggested elsewhere with the FormFields checkboxes, only with the Content Control ones.

For (c), something like this (not tested)

Make sure the On Exit macro is set for optional_item1 and the Calculate on Exit is also set. I don't think you need an On Entry macro as well.

Sub optional_item1_Click() 
  Call cb_Click("optional_item1") 
End Sub 

Sub cb_Click(ffname As String)
  ActiveDocument.Variables(ffname) = abs(int(ActiveDocument.FormFields(ffname).Checkbox.Value))
End Sub

In your Summa column, next to the checkbox, something like

{ ={ DOCVARAIABLE optional_item1 }*X2 }

where X2 is the cell that contains the 3,00 Euro value and all the { } are the special field code brace pairs that you can insert using ctrl-F9 on Windows Word.

BUT

Whether Word will correctly interpret your Euro value correctly depends on the regional settings of the user using your form, (decimal separator, group (thousands) separator, currency symbol and location of the currency symbol may all change Word's behaviour. Even within the Eurozone I think some of those things can vary. So if the user does not need to modify the multiplier 3,00 Euros in the cell, it may be better to plug the value directly into the { = } field:

{ ={ DOCVARAIABLE optional_item1 }*3 }

You can also use \# numeric formats to give you the format you want when positive or 0. But again, Word's numeric formatting facility is not locale-independent).

Those locale dependencies are also sometimes a reason for doing more in VBA rather than field codes.

回答2:

As AdamsTips found out elsewhere, replacing this

ActiveDocument.FormFields("myField").Result

by this

ActiveDocument.Bookmarks("myField").Range.Fields(1).Result

allows accessing the field without focusing it.