Search code examples
vbaform-fields

VBA Word: How to let VBA ignore 1000 separator while calculating formfields?

I need your help with this. I have a small code which sums up the value of 10 formfields. The format of this formfields is set with the 1000 separator. The problem that I'm running into is that VBA now returns a value like 1.000 as 1. In other words: 1+10+100+1.000=112. How can I adjust the code so that it ignores the 1000 separator while calculating those formfields? Any help is much appreciated!

ActiveDocument.FormFields("voattot").Result = Val(ActiveDocument.FormFields("betvoat1").Result) _
+ Val(ActiveDocument.FormFields("betvoat2").Result) + Val(ActiveDocument.FormFields("betvoat3").Result) _
+ Val(ActiveDocument.FormFields("betvoat4").Result) + Val(ActiveDocument.FormFields("betvoat5").Result) _
+ Val(ActiveDocument.FormFields("betvoat6").Result) + Val(ActiveDocument.FormFields("betvoat7").Result) _
+ Val(ActiveDocument.FormFields("betvoat8").Result) + Val(ActiveDocument.FormFields("betvoat9").Result) _
+ Val(ActiveDocument.FormFields("betvoat10").Result)
Solution

  • I'd Create a Seperate Function to parse the result string from the form field

    Function GetFieldValue(FieldName as string)
Dim FF As FormField 
Dim Result As String
Dim Value As Double


Set FF = ActiveDocument.Formfields(FieldName)'Find the Form Field
Result = FF.Result                           'Get the Text from the Form Field
Result = Replace(Result, ",", "")            'Remove any commas
If Trim(Result) ="" Then
    GetFieldValue= 0                         'Return the numeric valu
ElseIf Isnumeric(Result) Then
    Value = CDbl(Result)                         'Convert to numeric value
    GetFieldValue= Value                          'Return the numeric value
Else
    Debug.Print "Not IsNumeric(""" & Result & """)"
    GetFieldValue= 0
End If
End Function

    And then you can write your statement as

    ActiveDocument.FormFields("voattot").Result = GetFieldValue("betvoat1") _
+ GetFieldValue("betvoat2") + GetFieldValue("betvoat3") _
+ GetFieldValue("betvoat4") + GetFieldValue("betvoat5") _
+ GetFieldValue("betvoat6") + GetFieldValue("betvoat7") _
+ GetFieldValue("betvoat8") + GetFieldValue("betvoat9") _
+ GetFieldValue("betvoat10")

    This also means you can put error trapping into your Function to deal with empty or non numeric values or missing form fields. Whether it's worth bothering with that is up to you