Convert percentage into words

[DavG63]

It seems like I have nothing but questions these days - thank goodness for this forum!

I have a Userform with a field for the user to insert a percentage rate, i.e. "1.0", "3.5", "0.47" etc which is then displayed in the document as:-

"ONE (1.0) PER CENTUM PER ANNUM"
"THREE POINT FIVE (3.5) PER CENTUM PER ANNUM"
"ZERO POINT FOUR SEVEN (0.47) PER CENTUM PER ANNUM"

I've just realised that using my usual method of CardText to convert the number into words isn't going to work for percentages as "0.47" for example will read "forty-seven" instead of "four seven".

Is there a format that I can use to make this change?

Thanks for all the assistance.

Dav

[gmayor]

See http://www.gmayor.com/formatting_word_fields.htm and you may have to resort to more lateral thinking, as this time I don't think Paul will have a silver bullet - though no doubt he will be along later to confirm ... or not

In the meantime you will undoubtedly find his work at http://www.gmayor.com/Zips/Word%20Field%20Maths.zip and http://www.gmayor.com/Zips/DateCalc.zip useful

[macropod]

Here's a stand-alone field code for a proof-of-concept. As coded it will handle up to three decimal places.

{QUOTE{ASK Percent}{=INT(Percent) \* CardText}{SET Rmndr {=MOD(Percent,1)}}{IF{Rmndr}> 0 " point {=INT(Rmndr*10) \* CardText}{SET Rmndr {=MOD(Rmndr*10,1)}}{IF{Rmndr}> 0 " {=INT(Rmndr*10) \* CardText}"}{SET Rmndr {=MOD(Rmndr*10,1)}}{IF{Rmndr}> 0 " {=INT(Rmndr*10) \* CardText}"}"}}

To extend the precision, you could simply keep adding more sets of the highlighted:

{SET Rmndr {=MOD(Rmndr*10,1)}}{IF{Rmndr}> 0 " {=INT(Rmndr*10) \* CardText}"}

[gmaxey]

Since you are writing a userform text field input to the document, perhaps you could jus use a function:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
  MsgBox UCase(fcnSpellOut("0.47")) & " (0.47) PERCENT PER ANNUM"
lbl_Exit:
  Exit Sub
End Sub
Function fcnSpellOut(strIn As String) As String
Dim lngIndex As Long
  fcnSpellOut = vbNullString
  For lngIndex = 1 To Len(strIn)
    Select Case Mid(strIn, lngIndex, 1)
      Case ".": fcnSpellOut = fcnSpellOut & " point"
      Case "0": fcnSpellOut = fcnSpellOut & " Zero"
      Case "4": fcnSpellOut = fcnSpellOut & " Four"
      Case "7": fcnSpellOut = fcnSpellOut & " Seven"
    End Select
    fcnSpellOut = Trim(fcnSpellOut)
  Next
End Function

[DavG63]

Hi Paul

Thanks very much for your suggestion. Before I play around with functions which I've never tried to use before I thought I'd try your field code.

In my document I had two fields, one for balance and one for interest rate. I've already had assistance in splitting the balance and turning this from a numeric string into words as below:-

Dim iSumOne As String 
iSumOne = Me.ComboBox11.Value
oVars("SumOnePounds") = Split(iSumOne, Chr(46))(0)
If InStr(1, iSumOne, Chr(46)) > 0 Then
    oVars("SumOnePence") = Split(iSumOne, Chr(46))(1)
      Else
    oVars("SumOnePence") = vbNullString
       End If
If Me.ComboBox11.Value = vbNullString And Me.ComboBox12.Value = vbNullString Then
iSumOne = vbNullString
oVars("SumOnePounds") = vbNullString
oVars("SumOnePence") = vbNullString
End If
End If

So what I tried was just doing the same with the interest figure but it was at that point I realised I was stuck:-

Dim iSumOne As String, iIntOne As StringiSumOne = Me.ComboBox11.Value
iIntOne = Me.ComboBox12.Value 'The number to be processed
oVars("SumOnePounds") = Split(iSumOne, Chr(46))(0)
oVars("IntOnePounds") = Split(iIntOne, Chr(46))(0)
If InStr(1, iSumOne, Chr(46)) > 0 Then
    oVars("SumOnePence") = Split(iSumOne, Chr(46))(1)
    oVars("IntSumPence") = Split(iIntOne, Chr(46))(1)
   Else
    oVars("SumOnePence") = vbNullString
    oVars("IntOnePence") = vbNullString
   End If
If Me.ComboBox11.Value = vbNullString And Me.ComboBox12.Value = vbNullString Then
iSumOne = vbNullString
iIntOne = vbNullString
oVars("SumOnePounds") = vbNullString
oVars("SumOnePence") = vbNullString
oVars("IntOnePounds") = vbNullString
oVars("IntOnePence") = vbNullString
End If
End If

Am I right in thinking that in your example I can just have one DocVariable for interest without having to mess about splitting it and the formatting itself will do the heavy lifting?

Sorry for all the questions, I'm getting into deeper water here than I've done previously.

Thanks

Dav

[gmaxey]

I'm Greg. Paul is the smart guy.

If you want to use DocVariables fields (why?) then you can still use the function (which you will need to complete out):

Private Sub CommandButton1_Click()
  With ActiveDocument
    .Variables("Interest").Value = fcnSpellOut(ComboBox1.Value)
    .Fields.Update
  End With
End Sub
Function fcnSpellOut(strIn As String) As String
Dim strTemp As String
Dim lngIndex As Long
    strTemp = vbNullString
    For lngIndex = 1 To Len(strIn)
        Select Case Mid(strIn, lngIndex, 1)
        Case ".": strTemp = strTemp & " point"
        Case "0": strTemp = strTemp & " Zero"
        Case "4": strTemp = strTemp & " Four"
        Case "7": strTemp = strTemp & " Seven"
        End Select
        fcnSpellOut = UCase(Trim(strTemp) & " (" & strIn & ") PERCENT PER ANNUM")
    Next
End Function

In the document put { DocVariable Interest }

[macropod]

Hi Paul

Thanks very much for your suggestion. Before I play around with functions which I've never tried to use before I thought I'd try your field code.

...

Am I right in thinking that in your example I can just have one DocVariable for interest without having to mess about splitting it and the formatting itself will do the heavy lifting?

You could indeed do that. Suppose you have a DocVariable named 'Percent'. The field code for that would be:

{QUOTE{=INT({DOCVARIABLE Percent}) \* CardText}
{SET Rmndr {=MOD({DOCVARIABLE Percent},1)}}
{IF{Rmndr}> 0 " point {=INT(Rmndr*10) \* CardText}
{SET Rmndr {=MOD(Rmndr*10,1)}}
{IF{Rmndr}> 0 " {=INT(Rmndr*10) \* CardText}"}
{SET Rmndr {=MOD(Rmndr*10,1)}}
{IF{Rmndr}> 0 " {=INT(Rmndr*10) \* CardText}"}"}}

Your VBA code could then simply update the DocVariable, then issue 'ActiveDocument.Fields.Update' to update the content in the body.

Note: I've laid out the field code with line breaks for clarity, but you don't need/want them in practice.

[DavG63]

Hi Greg/Paul

I'm pleased to say that after a little bit of work I was able to get the conditional format to work. Oh and to answer your question Greg, DocVariables were the first thing I learned how to use in VBA. I'm entirely self taught so it's just a case of my using what I know rather than trying to reinvent the wheel.

Thanks so much to you both for your help, I really do appreciate it.

Dav

[gmaxey]

Ok. Say you wanted to use a content control titled "Interest" instead of a DocVariable field:

Private Sub CommandButton1_Click() 
    ActiveDocument.SelectContentControlsByTitle("Interest").Item(1).Range.Text = fcnSpellOut(ComboBox1.Value) 
End Sub

[DavG63]

I've managed to get it working so that it now works when there's an interest rate.

If you remember my prior question about deleting out rows in tables however you'll probably know where this question is going. If the interest field is left blank because that row of the table isn't going to be displayed, it now won't delete because that field leaves behind text.

My usual

{IF {DocVariable IntOne }="Error!* ""

Isn't doing the trick this time and instead just makes the whole thing disappear.

One step forward, two steps back!

Any ideas of a way to get round it?

[macropod]

The fairly obvious answer is to not rely on the cell's content to determine whether to delete the row - rely on the value of whatever you're using to populate (or not) the Document Variable instead. Of course, field coding can be used to suppress the error message, but why bother with that if the row is destined for the bin?

[DavG63]

Yeah I get what you're saying. Is there a simple code then that I could use that would delete row two if a variable is blank and row three if another is also blank?

[macropod]

That's pretty basic. For example:
If ComboBox1.Value = "" Then ActiveDocument.Tables(1).Rows(5).Delete
where 1 is the table # and 5 is the row #.

[DavG63]

Thanks Paul, I'll give that a try.

[macropod]

Of course, you could just output 0 to IntOne, and have 'zero' (plus whatever other text you want) output via the DOCVARIABLE field, thereby obviating the problem...