CODEWITHSUNDEEP
vbams-word
tytyf
tytyf

Reputation: 342

Convert string to double in word (vba)

I am trying to convert a text in a word document to be a double, so I can do currency formatting on it. I receive this text from a mail merge. How would I create a macro that can receive this text and return it as a number? I'm unfamiliar with word, and VBA script. What I have made so far is 

Function stringToDouble(baseString As String)
    Dim num As Double

    num = Val(baseString)

    stringToDouble = num

End Function

I'm not sure how I would call this macro. Because it takes a parameter it does not show up in the macro table. I may be completely off on how to convert text to a double in word, but any help is appreciated. Thanks. Please comment for any clarifications.

Upvotes: 0

Views: 1300

Answers (2)

macropod
macropod

Reputation: 13505

You don't need a macro for this!!! All you need do is learn how to use formatting switches in Word fields.

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:

  • select the mergefield;
  • press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
  • edit the field so that you get {MERGEFIELD MyData # $,0.00} (or whatever other numeric format you prefer - see below);
  • position the cursor anywhere in this field and press F9 to update it.

Note 1: The '# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:

  • # 0 for rounded whole numbers
  • # ,0 for rounded whole numbers with a thousands separator
  • # ,0.00 for numbers accurate to two decimal places, with a thousands separator
  • # $,0 for rounded whole dollars with a thousands separator
  • # "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

Note 2: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'. If you use a final ';' in the formatting switch with nothing following, (eg # "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Note 3: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. # "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output with the decimal alignment occurring after the $ sign.

For more Mailmerge Tips & Tricks, see: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

Upvotes: 1

ChrisB
ChrisB

Reputation: 3205

If you want to convert the number to a Double data type then try this:

Function StringToDouble(ByVal baseString As String) As Double
    StringToDouble = VBA.CDbl(baseString)
End Function

If you're only concerned about formatting currency, convert the string to the Currency data type like this:

Function StringToCurrency(ByVal baseString As String) As Currency
    StringToCurrency = VBA.CCur(baseString)
End Function

You will still need to format the number but both functions give you a number that can be formatted.

Here's an example that also gives you a string formatted as USD (e.g. $4,999.75). It requires the StringToCurrency function above.

Sub test()
    Dim stringNum As String
    stringNum = "4,999.754501"
    Debug.Print "stringNum=" & stringNum                ' outputs 4,999.754501

    Dim currencyNum As Currency
    currencyNum = StringToCurrency(stringNum)           ' outputs 4999.7545
    Debug.Print "currencyNum=" & currencyNum

    Dim formattedString As String
    formattedString = Format$(currencyNum, "$#,##0.00")
    Debug.Print "formattedString=" & formattedString    ' outputs $4,999.75
End Sub

Upvotes: 0

Related Questions