# Converting Numbers to Words Part III

My tests work from 0-99. The next test will test numbers between 100-199.

A haphazard selection of numbers including the edge cases.

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.

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.

Instead of hardcoding “one hundred”, I’ll pull the property number from vaSingles. This also shows my brute force zero fix.

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.

Posted in VBA

## 2 thoughts on “Converting Numbers to Words Part III”

1. snb says:

or ?
```vaTens = Split(" twenty thirty forty fifty sixty seventy eighty ninety") ```

2. Nigel Heffernan says:

I remember posting a recursive function for this to StackOverflow a while ago:

I think your code is more elegant, but you’ll run into issues with tens of thousands, and the hierarchical conventions for millions and billions. For example, you can express 2^50 as:

“Eleven Hundred and Twenty-Five Trillion, Eight Hundred and Ninety-Nine Billion, Nine Hundred and Six Million, Eight Hundred and Forty-Two Thousand, Six Hundred and Twenty One Trillion, Ninety-Nine Billion, Five Hundred and Eleven Million, Six Hundred and Twenty-Seven Thousand, Seven Hundred and Seventy-Six”

I do not anticipate that you will need to express this kind of number in text on a regular basis. However, there is some instructional value in demonstrating an application of recursion to beginners in programming.

``` Public Function SayNumber(ByVal InputNumber As Double, _ Optional DecimalPlaces As Integer = 0 _ ) As String ' Return the integer portion of the number in formatted English words ' Return the fractional part of the number as 'point' and a series of ' single-numeral words, up to the precision specified by 'DecimalPlaces' ' SayNumber(17241021505) ' "Seventeen Billion, Two Hundred and Forty-One Million, ' Twenty-One Thousand, Five Hundred and Five" ' SayNumber(Sqr(2), 6) ' "One point Four One Four Two One Four" ' Note that nothing after the decimal point will be returned if InputNumber is an integer ' Nigel Heffernan, December 2008 Application.Volatile False On Error Resume Next Dim arrDigits(0 To 9) As String Dim arrTeens(10 To 19) As String Dim arrTens(2 To 9) As String Dim i As Integer Dim i10 As Integer Dim i20 As Integer Dim dblRemainder As Double Dim dblMain As Double Dim iRemainder As Long Dim iMain As Long Dim dblFraction As Double Dim strFraction As String Dim strMinus As String Dim str1 As String Dim str2 As String Dim str3 As String If Application.EnableEvents = False Then Exit Function End If arrDigits(0) = "Zero" arrDigits(1) = "One" arrDigits(2) = "Two" arrDigits(3) = "Three" arrDigits(4) = "Four" arrDigits(5) = "Five" arrDigits(6) = "Six" arrDigits(7) = "Seven" arrDigits(8) = "Eight" arrDigits(9) = "Nine" arrTeens(10) = "Ten" arrTeens(11) = "Eleven" arrTeens(12) = "Twelve" arrTeens(13) = "Thirteen" arrTeens(14) = "Fourteen" arrTeens(15) = "Fifteen" arrTeens(16) = "Sixteen" arrTeens(17) = "Seventeen" arrTeens(18) = "Eighteen" arrTeens(19) = "Nineteen" arrTens(2) = "Twenty" arrTens(3) = "Thirty" arrTens(4) = "Forty" arrTens(5) = "Fifty" arrTens(6) = "Sixty" arrTens(7) = "Seventy" arrTens(8) = "Eighty" arrTens(9) = "Ninety" If InputNumber < 0 Then strMinus = "Minus " InputNumber = Abs(InputNumber) End If If DecimalPlaces < 1 Then strFraction = "" Else dblFraction = InputNumber - Fix(InputNumber) If dblFraction = 0 Then strFraction = "" Else strFraction = " point" str1 = Format(dblFraction, "0." & String(DecimalPlaces, "0")) For i = 1 To DecimalPlaces str2 = MID(str1, i + 2, 1) strFraction = strFraction & " " & arrDigits(CInt(str2)) str2 = "" Next i str1 = "" End If End If If InputNumber < 10 Then str1 = arrDigits(InputNumber) ElseIf InputNumber < 19 Then str1 = arrTeens(InputNumber) ElseIf InputNumber < 100 Then iMain = InputNumber \ 10 str1 = arrTens(iMain) iRemainder = InputNumber Mod 10 If iRemainder > 0 Then str2 = "-" & arrDigits(iRemainder) End If ElseIf InputNumber < 1000 Then iMain = InputNumber \ 100 str1 = arrDigits(iMain) & " Hundred" iRemainder = InputNumber Mod 100 If iRemainder > 0 Then str2 = " and " & SayNumber(iRemainder) End If ElseIf InputNumber < 2000 Then iMain = InputNumber \ 100 str1 = arrTeens(iMain) & " Hundred" iRemainder = InputNumber Mod 100 If iRemainder > 0 Then str2 = " and " & SayNumber(iRemainder) End If ElseIf InputNumber < 1000000 Then iMain = InputNumber \ 1000 str1 = SayNumber(iMain) & " Thousand" iRemainder = InputNumber Mod 1000 If iRemainder > 0 Then str2 = ", " & SayNumber(iRemainder) End If ElseIf InputNumber < (10 ^ 9) Then iMain = InputNumber \ (10 ^ 6) str1 = SayNumber(iMain) & " Million" iRemainder = InputNumber Mod (10 ^ 6) If iRemainder > 0 Then str2 = ", " & SayNumber(iRemainder) End If ElseIf InputNumber < (10 ^ 12) Then ' we'll hit the LongInt arithmetic operation limit at ~2.14 Billion str3 = Format(InputNumber, "0") dblMain = CDbl(Left(str3, Len(str3) - 9)) str1 = SayNumber(dblMain) & " Billion" dblRemainder = CDbl(Right(str3, 9)) If dblRemainder > 0 Then str2 = ", " & SayNumber(dblRemainder) End If ElseIf InputNumber < 1.79769313486231E+308 Then ' This will generate a recursive string of 'Trillions' str3 = Format(InputNumber, "0") dblMain = CDbl(Left(str3, Len(str3) - 12)) str1 = SayNumber(dblMain) & " Trillion" dblRemainder = CDbl(Right(str3, 12)) If dblRemainder > 0 Then str2 = ", " & SayNumber(dblRemainder) End If Else ' exceeds the specification for double-precision floating-point variables str1 = "#Overflow." End If SayNumber = strMinus & str1 & str2 & strFraction End Function ```

`I will leave the corresponding code in French as an exercise for the reader.`

Posting code? Use <pre> tags for VBA and <code> tags for inline.