2 thoughts on “Converting Numbers to Words Part III”

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


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

   http://stackoverflow.com/a/5232125

   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.