Convert numeric string to words

[DavG63]

Hi

I have a document with currency amounts in British Pounds entered into a userform, i.e £123.45 - and what I'm looking for is to be able to display that currency figure as ONE HUNDRED AND TWENTY-THREE POUNDS AND FORTY-FIVE PENCE.

I'm aware of the \*Dollartext method which I can add to my DocVariable, but the "0/100" in cents doesn't really suit my purposes. I checked for a UK equivalent of dollartext but sadly couldn't find one - boo Microsoft! :-)

Does anyone happen to know how I would go about converting this?

Thanks

Dav

[gmayor]

You can use the Cardtext switch (http://www.gmayor.com/formatting_word_fields.htm - { = 123 \*Cardtext} pounds and { =45 \*Cardtext } pence). Better still see http://www.gmayor.com/cash_values_as_text.htm

[DavG63]

Thanks Graham, unfortunately I don't have the option of installing additional add-ins to Word.

[gmayor]

In that case see my first suggestion and the first of my links.

[DavG63]

Thanks Graham, I will give this a go and see how I get on.

[DavG63]

Sorry Graham, I'm still having difficulty with this. I've outlined my code section below to give some context. I have one big button at the end of my form which dumps the figures into the document - there can be up to twenty in any one document. I'd intended it to be at this point the macro should convert those entries (1-20) into text to appear in up to 20 other DocVariables - so you have 20 that deal with the numerical values and 20 that deal with the text values.




Set oVars = ActiveDocument.Variables

oVars("CompanyName") = Me.ComboBox1.Value
oVars("CompanyAddOne") = Me.ComboBox4.Value
oVars("CompanyAddTwo") = Me.ComboBox5.Value
oVars("CompanyRef") = Me.ComboBox6.Value


oVars("SupplierName") = Me.ComboBox85.Value
oVars("SupplierRange") = Me.ComboBox86.Value


oVars("CustomerNameOne") = Me.ComboBox7.Value
oVars("CustomerOneAddOne") = Me.ComboBox10.Value
oVars("CustomerOneAddTwo") = Me.ComboBox11.Value
oVars("CustomerOneRef") = Me.ComboBox12.Value
oVars("CustomerNameTwo") = Me.ComboBox13.Value
oVars("CustomerTwoAddOne") = Me.ComboBox16.Value
oVars("CustomerTwoAddTwo") = Me.ComboBox17.Value
oVars("CustomerTwoRef") = Me.ComboBox18.Value
oVars("CustomerNameThree") = Me.ComboBox19.Value
oVars("CustomerThreeAddOne") = Me.ComboBox22.Value
oVars("CustomerThreeAddTwo") = Me.ComboBox23.Value
oVars("CustomerThreeRef") = Me.ComboBox24.Value


oVars("SumOne") = Me.ComboBox25.Value


oVars("iVarOne") = iVarOne
oVars("iVarTwo") = iVarTwo
oVars("iVarThree") = iVarThree
oVars("iVarFour") = iVarFour
oVars("iVarFive") = iVarFive
oVars("iVarSix") = iVarSix
oVars("iVarSeven") = iVarSeven
oVars("iVarEight") = iVarEight


Me.Hide


ActiveDocument.Fields.Update


Set oVars = Nothing


Unload Me



I must admit to being a little lost. I'm assuming that I don't have to put the whole Cardtext example you sent me directly into the DocVariable? I was expecting that I'd have to put an IF Statement somewhere in the above macro to force the conversion and update a separate DocVariable.

Any clarification you can give me would be great.

Dav

[gmayor]

I don't understand your code

oVars("iVarOne") = iVarOne
oVars("iVarTwo") = iVarTwo
oVars("iVarThree") = iVarThree
oVars("iVarFour") = iVarFour
oVars("iVarFive") = iVarFive
oVars("iVarSix") = iVarSix
oVars("iVarSeven") = iVarSeven
oVars("iVarEight") = iVarEightiVarOne to iVarEight are not defined.

For a given number if you enter the following fields (CTRL+F9 for each bracket pair)

{ IF { DOCVARIABLE varNumPounds } > 0 "{ DOCVARIABLE varNumPounds \*CARDTEXT \*UPPER } POUNDS" } { IF { DOCVARIABLE varNumPence } > 0 "AND { DOCVARIABLE varNumPence \*CARDTEXT \*UPPER} { IF { DOCVARIABLE varNumPence } = 1 "PENNY" "PENCE" }" "ONLY" }

in your document (obviously the variable names will need to be changed for multiple numbers)
then the following will write the values to those variables. The code does not contain error handling for strings that are not numbers or which do not have the correct


Dim sNum As String
sNum = "124.45" 'The number to be processed
ActiveDocument.Variables("VarNumPounds").Value = Split(sNum, Chr(46))(0)
If InStr(1, sNum, Chr(46)) > 0 Then
ActiveDocument.Variables("VarNumPence").Value = Split(sNum, Chr(46))(1)
Else
ActiveDocument.Variables("VarNumPence").Value = "0"
End If
ActiveDocument.Fields.Update

[DavG63]

Graham, I can't thank you enough. It took me about a half hour of messing about before I realised I hadn't spelled DocVariable right :crying:, but once I changed that it worked exactly as I'd hoped it would.

Thanks very much for your expert assistance as always.

Dav

[SamT]

If you put Option Explicit at the top of you code page, then when you use the Menu Debug >> Compile VBA Project, it will find the first misspelled word for you. Use Compile as often as needed and it will find many errors in code.