See Converting Numbers to Words Part II
My tests work from 0-99. The next test will test numbers between 100-199.
Sub TEST_OneHundred()
Debug.Assert NumbersToWords(100) = "one hundred"
Debug.Assert NumbersToWords(110) = "one hundred ten"
Debug.Assert NumbersToWords(119) = "one hundred nineteen"
Debug.Assert NumbersToWords(120) = "one hundred twenty"
Debug.Assert NumbersToWords(121) = "one hundred twenty-one"
Debug.Assert NumbersToWords(150) = "one hundred fifty"
Debug.Assert NumbersToWords(188) = "one hundred eighty-eight"
Debug.Assert NumbersToWords(199) = "one hundred ninety-nine"
End Sub
A haphazard selection of numbers including the edge cases.
Function NumbersToWords(ByVal dNumbers As Double) As String
Dim vaSingles As Variant
Dim vaTens As Variant
Dim sReturn As String
vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
vaTens = Split("NA,NA,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
If dNumbers >= 100 Then
sReturn = "one hundred"
If dNumbers Mod 100 <> 0 Then
If dNumbers – 100 > 19 Then
sReturn = sReturn & Space(1) & vaTens((dNumbers – 100) \ 10)
If (dNumbers – 100) Mod 10 <> 0 Then
sReturn = sReturn & "-" & vaSingles((dNumbers – 100) – (((dNumbers – 100) \ 10) * 10))
End If
Else
sReturn = sReturn & Space(1) & vaSingles(dNumbers – 100)
End If
End If
ElseIf dNumbers > 19 Then
sReturn = vaTens(dNumbers \ 10)
If dNumbers Mod 10 <> 0 Then
sReturn = sReturn & "-" & vaSingles(dNumbers – ((dNumbers \ 10) * 10))
End If
Else
sReturn = vaSingles(dNumbers)
End If
NumbersToWords = Trim$(sReturn)
End Function
And all tests pass. Back in the first post of this series I said that I hoped it would be obvious when I need to refactor. Well if this isn’t a frying pan to the face, I don’t know what is. Way too much repetition, for one. I need to introduce a “remainder” variable, so that once I process the hundred part, I can send the remainder to process the tens, and the remainder from that to the less than 19 part.
Function NumbersToWords(
ByVal dNumbers
As Double)
As String
Dim vaSingles As Variant
Dim vaTens As Variant
Dim sReturn As String
Dim dRemainder As Double
vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
dRemainder = dNumbers
If dRemainder >= 100 Then
sReturn = "one hundred" & Space(1)
dRemainder = dRemainder – (dRemainder \ 100) * 100
End If
If dRemainder > 19 Then
sReturn = sReturn & vaTens(dRemainder \ 10)
dRemainder = dRemainder – (dRemainder \ 10) * 10
End If
If dRemainder > 0 Then
If Right(sReturn, 1) = "y" Then
sReturn = sReturn & "-"
End If
sReturn = sReturn & vaSingles(dRemainder)
End If
NumbersToWords = Trim$(sReturn)
End Function
That looks much better, but it doesn’t pass the zero test. I don’t like special cases, but zero might just be one, so I’m going to force it. My conditional on whether to include a hyphen checks to see if the answer so far ends in “y”. That seems a little hokey, but it works. I could test for mod10 and set a Boolean variable in the If block above, but I’m not sure what I gain, so there it stays.
Refactoring in this way also makes the next bit of testing code painfully obvious. I’m hardcoding “one hundred”, but with vaSingles sitting right there, I don’t know why I can’t go above 199 pretty easily. So I’ll write that next test.
Sub TEST_Hundreds()
Debug.Assert NumbersToWords(200) = "two hundred"
Debug.Assert NumbersToWords(310) = "three hundred ten"
Debug.Assert NumbersToWords(419) = "four hundred nineteen"
Debug.Assert NumbersToWords(520) = "five hundred twenty"
Debug.Assert NumbersToWords(621) = "six hundred twenty-one"
Debug.Assert NumbersToWords(750) = "seven hundred fifty"
Debug.Assert NumbersToWords(888) = "eight hundred eighty-eight"
Debug.Assert NumbersToWords(999) = "nine hundred ninety-nine"
End Sub
Instead of hardcoding “one hundred”, I’ll pull the property number from vaSingles. This also shows my brute force zero fix.
Function NumbersToWords(
ByVal dNumbers
As Double)
As String
Dim vaSingles As Variant
Dim vaTens As Variant
Dim sReturn As String
Dim dRemainder As Double
vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
If dNumbers = 0 Then
sReturn = "zero"
Else
dRemainder = dNumbers
If dRemainder >= 100 Then
sReturn = sReturn & vaSingles(dRemainder \ 100) & " hundred "
dRemainder = dRemainder – (dRemainder \ 100) * 100
End If
If dRemainder > 19 Then
sReturn = sReturn & vaTens(dRemainder \ 10)
dRemainder = dRemainder – (dRemainder \ 10) * 10
End If
If dRemainder > 0 Then
If Right(sReturn, 1) = "y" Then
sReturn = sReturn & "-"
End If
sReturn = sReturn & vaSingles(dRemainder)
End If
End If
NumbersToWords = Trim$(sReturn)
End Function
All tests pass. And the code doesn’t look too bad. Only infinity numbers left to test. Here’s what my main testing procedure looks like now, as if you couldn’t guess.
Sub TEST_All()
TEST_Singles
TEST_Tens
TEST_OneHundred
TEST_Hundreds
Debug.Print "tests passed"
End Sub
