Converting Numbers to Words Part V

See Converting Numbers to Words Part IV

No need to bite this one off in small chunks. Just need to make sure the triplets processing works at the next level.

Sub TEST_Millions()

Debug.Assert NumbersToWords(1000000) = "one million"
Debug.Assert NumbersToWords(1000001) = "one million one"
Debug.Assert NumbersToWords(20000000) = "twenty million"
Debug.Assert NumbersToWords(55555000) = "fifty-five million five hundred fifty-five thousand"
Debug.Assert NumbersToWords(999999999) = "nine hundred ninety-nine million nine hundred ninety-nine thousand nine hundred ninety-nine"

End Sub

I’m just going to add a new If block for millions that looks a lot like the thousands If block. Of course I’ll be using exponents so I don’t have to type all those zeros.

Function NumbersToWords(ByVal dNumbers As Double) As String

Dim sReturn As String
Dim dRemainder As Double

If dNumbers = 0 Then
sReturn = "zero"
Else

dRemainder = dNumbers

If dRemainder >= 10 ^ 6 Then
sReturn = ProcessTriplet(dRemainder \ 10 ^ 6, "million")
dRemainder = dRemainder - ((dRemainder \ 10 ^ 6) * 10 ^ 6)
End If

If dRemainder >= 1000 Then
sReturn = sReturn & Space(1) & ProcessTriplet(dRemainder \ 1000, "thousand")
dRemainder = dRemainder - ((dRemainder \ 1000) * 1000)
End If

If dRemainder > 0 Then
sReturn = sReturn & Space(1) & ProcessTriplet(dRemainder)
End If

End If

NumbersToWords = Trim$(sReturn)

End Function

All tests passed. The rest should be easy. I’m going to go a little sparse on the next tests.

Sub TEST_More()

Debug.Assert NumbersToWords(1 * 10 ^ 9) = "one billion"
Debug.Assert NumbersToWords(1000000001) = "one billion one"
Debug.Assert NumbersToWords(999999999999999#) = "nine hundred ninety-nine trillion nine hundred ninety-nine billion nine hundred ninety-nine million nine hundred ninety-nine thousand nine hundred ninety-nine"

End Sub

I could create a new If block for each triplet, but I already know I’ll be refactoring, so what’s the point. I need to loop through however many triplets are there and process them.

Function NumbersToWords(ByVal dNumbers As Double) As String

Dim sReturn As String
Dim dRemainder As Double
Dim vaTriplets As Variant
Dim i As Long

vaTriplets = Split(",,,thousand,,,million,,,billion,,,trillion", ",")

If dNumbers = 0 Then
sReturn = "zero"
Else

dRemainder = dNumbers

For i = 12 To 0 Step -3
If dRemainder >= 10 ^ i Then
sReturn = sReturn & Space(1) & ProcessTriplet(dRemainder \ 10 ^ i, vaTriplets(i))
dRemainder = dRemainder - ((dRemainder \ 10 ^ i) * 10 ^ i)
End If
Next i

End If

NumbersToWords = Trim$(sReturn)

End Function

Error: Overflow. I originally passed in a Double so I could do decimals, but never did the decimals. Anyway, it’s the integer division operator (\) that’s causing the problem. When you use a floating point number, like a Double, in an integer division expression, VBA casts it as a Long first. So anything more than 2.4 billion won’t work. Fortunately, MS has a fix.

Function NumbersToWords(ByVal dNumbers As Double) As String

Dim sReturn As String
Dim dRemainder As Double
Dim vaTriplets As Variant
Dim i As Long
Dim lFixed As Long

vaTriplets = Split(",,,thousand,,,million,,,billion,,,trillion", ",")

If dNumbers = 0 Then
sReturn = "zero"
Else

dRemainder = dNumbers

For i = 12 To 0 Step -3
If dRemainder >= 10 ^ i Then
lFixed = Fix(Int(dRemainder + 0.5) / 10 ^ i)
sReturn = sReturn & Space(1) & ProcessTriplet(lFixed, vaTriplets(i))
dRemainder = dRemainder - (lFixed * 10 ^ i)
End If
Next i

End If

NumbersToWords = Trim$(sReturn)

End Function

All tests passed. And that’s it. I could add decimals, I suppose. Or even larger numbers.

The test-first methodology was pretty enjoyable, I have to say. This isn’t especially complicated code, but biting it off in small chunks made things flow nicely.

Posted in VBA

3 thoughts on “Converting Numbers to Words Part V

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax