Word VBA and Currency RRS feed

  • Question

  • Hi

    I have a Word doc with VBA script to populate numbers from a Userform to a Table in the Word doc. This works perfectly, but the problem I have is with the currency format of the number. It works well if the format in the doc is the same as the regional settings on my PC, but the problem comes in when the template runs on a machine with different regional settings, specifically on the decimal symbols and number separator. I cannot a way to force this setting in Word just for this template so that the macro works from any machine. Also Word 2016 doesn't have the option to set the decimal and separator symbol just for Word.

    Can someone please help me with this problem I have been battling with for several weeks now. 

    Kind regards


    Friday, December 9, 2016 2:32 PM

All replies

  • I did something like this for Word 2003.  There is probably a better way now.  I only had to do it for a few different formats.

    You can get the country code with this:

    cCode = Application.International(xlCountrySetting)

    Then I created a function with formatting options for each country code

    fValue = Format(value, "#,##0.00;(#,##0.00)")

    Friday, December 9, 2016 4:09 PM
  • While you are not clear about the specific problem you are having with decimal and thousands characters, you can certainly get that information in Word - see for example Format Currency

    There are also issues with some currencies that don't use western digit groupings.  What is in the form field and how exactly are you writing the value from the form to the document? What do you expect to see in the document regardless of international considerations.

    Graham Mayor - Word MVP

    Saturday, December 10, 2016 5:11 AM
  • The form field is just text and linked to the table in word via DOCVARIABLES. I tried using Format(Value, "$ # ##0.00;($ # ##0.00)") but if my regional settings are not the same as the format above then it adds a decimal where there shouldn't be one. I then use =PRODUCT(LEFT) to multiply the values in the cells but obviously it calculates wrong.
    Saturday, December 10, 2016 5:18 AM
  • The problem is not so much the VBA, which doesn't care too much about the regional settings, but the field switches used on the fields in the table, which do. The answer therefore is to perform all the calculations in VBA and write the final values to the table cells, either as docvariable fields or directly.

    For the sake of argument, consider a userform with three text values. The user may enter the numbers in those text fields using the local n,nnn,nnn.nn or n.nnn.nnn,nn formats and VBA will total them regardless, using the following code to add the three values, format them as required and write the results to docvariables

    The DocVariable fields in the appropriate cells of the table (without formatting switches) will reproduce the values from the macro, but will format them in the document according to the Windows Regional settings.

    The only provisos are that the user must enter values using his own regional settings and if the region is subsequently changed, the values in the fields don't change (unless the macro is run again).

    Sub Macro1()
    Dim strValue1 As String
    Dim strValue2 As String
    Dim strValue3 As String
    Dim lngVal1 As Long
    Dim lngVal2 As Long
    Dim lngVal3 As Long
    Dim strTotal As String
        With UserForm1
            strValue1 = .TextBox1.Value
            lngVal1 = .TextBox1.Value
            strValue1 = Format(strValue1, "$#,###,##0.00;($#,###,##0.00)")
            ActiveDocument.Variables("Value1").Value = strValue1
            strValue2 = .TextBox2.Value
            lngVal2 = .TextBox2.Value
            strValue2 = Format(strValue2, "$#,###,##0.00;($#,###,##0.00)")
            ActiveDocument.Variables("Value2").Value = strValue2
            strValue3 = .TextBox3.Value
            lngVal3 = .TextBox3.Value
            strValue3 = Format(strValue3, "$#,###,##0.00;($#,###,##0.00)")
            ActiveDocument.Variables("Value3").Value = strValue3
            strTotal = lngVal1 + lngVal2 + lngVal3
            strTotal = Format(strTotal, "$#,###,##0.00;($#,###,##0.00)")
            ActiveDocument.Variables("Total").Value = strTotal

        End With
        Unload UserForm1
    End Sub

    Graham Mayor - Word MVP

    Saturday, December 10, 2016 10:03 AM