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.
