Converting Numbers to Words Part V

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

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

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

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

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

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

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.