## 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.

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.

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.

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.

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.

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.

Okay, so how does it cope with 2^50 ?

:)

Write me a check for that amount and I’ll figure it out.

[…] Kusleika finished his 5-part series on converting Numbers to Words in Excel […]