See Converting Numbers to Words Part III
On to the thousands:
|
1 2 3 4 5 6 7 8 9 10 |
Sub TEST_Thousands() Debug.Assert NumbersToWords(1000) = "one thousand" Debug.Assert NumbersToWords(1001) = "one thousand one" Debug.Assert NumbersToWords(1099) = "one thousand ninety-nine" Debug.Assert NumbersToWords(1200) = "one thousand two hundred" Debug.Assert NumbersToWords(1310) = "one thousand three hundred ten" Debug.Assert NumbersToWords(1999) = "one thousand nine hundred ninety-nine" End Sub |
Every triplet of numbers follow the same rules, and I’ve already tested 0-999. I need to take all that relevant code and put it in a callable function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Function ProcessTriplet(ByVal dNumber As Double, Optional ByVal sSuffix As String) As String Dim sReturn As String Dim vaSingles As Variant, vaTens As Variant 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 dNumber >= 100 Then sReturn = sReturn & vaSingles(dNumber \ 100) & " hundred " dNumber = dNumber - (dNumber \ 100) * 100 End If If dNumber > 19 Then sReturn = sReturn & vaTens(dNumber \ 10) dNumber = dNumber - (dNumber \ 10) * 10 End If If dNumber > 0 Then If Right(sReturn, 1) = "y" Then sReturn = sReturn & "-" End If sReturn = sReturn & vaSingles(dNumber) End If sReturn = sReturn & Space(1) & sSuffix ProcessTriplet = Trim(sReturn) End Function |
That’s the same old code, just put into a function. The triplet of numbers is passed in and the suffix (currently only “thousand”) is passed in. Now I just need to divide my number into triplets, process them, and concatenate the answers.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 >= 1000 Then sReturn = 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 |
If my numbers is 1,000 or more, I process the thousands triplet and figure the remainder. Then I process the remainder as its own triplet. I guess processing triplets in this way makes my next set of tests already pass.
|
1 2 3 4 5 6 7 8 9 10 |
Sub TEST_TenThousands() Debug.Assert NumbersToWords(10000) = "ten thousand" Debug.Assert NumbersToWords(10001) = "ten thousand one" Debug.Assert NumbersToWords(20099) = "twenty thousand ninety-nine" Debug.Assert NumbersToWords(30200) = "thirty thousand two hundred" Debug.Assert NumbersToWords(42310) = "forty-two thousand three hundred ten" Debug.Assert NumbersToWords(99999) = "ninety-nine thousand nine hundred ninety-nine" End Sub |
Yep, already passing. On deck, the millions.
It’s a pity you can’t use .Net. There’s a very nice library called Humanizer that does this, as well as many other capabilities: http://humanizr.net/#number-to-words