Converting Numbers to Text

Several of my applications include a feature to print directly on pre-printed business check stock.  One requirement for such a feature is to convert numbers to their English equivalents, such as 3 -> "Three."

When converting numbers to text for checks, it's important to convert the fractional portion, too, even if there is no fractional amount.  For example, 5.00 would become "Five and NO/100."

To implement this feature, I wrote NumToString.  This function converts to a text representation any number or numeric string within the range of  -2,147,483,647.99 to 2,147,483,647.99.  So long as our clients are writing checks in US Dollars and not Venezuelan Bolivars, I don't think I need to worry about them writing checks for more than two billion dollars in the near future.

The function below is also a great example of my DocTests in action.  It illustrates why DocTests are such a powerful feature.  They pack a strong signal to noise ratio.  What's more, the fact that they are testable means that you don't have to worry about the comments ever getting out of sync with the code.

Oh, and if you're new here, there's a reason you won't see error handling with most of my code.

Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : NumToString
' DateTime  : 3/25/2009 12:39
' Author    : Mike Wolfe <mike@nolongerset.com>
' Purpose   : Converts a numeric value to its string equivalent.
' Notes     - Constrained to Long integer range (+/-2 ^ 31 - 1)
'           - Need to ensure numbers are rounded to two decimal places before passing to function,
'               because rounding behavior is inconsistent:
'                   NumToString(.575) --> Zero and 57/100
'                   NumToString(.595) --> Zero and 60/100
'           - ShowNo100 = True  'displays " and NO/100" for integers and "Zero" for 0
'           - ShowNo100 = False 'hides " and NO/100" for integers and "" for 0
'>>> NumToString(13463.46)
' Thirteen Thousand Four Hundred Sixty-Three and 46/100
'>>> NumToString(0)
' Zero and NO/100
'>>> NumToString(0,False)
'
'>>> NumToString(3)
' Three and NO/100
'>>> NumToString(3,False)
' Three
'>>> NumToString(1873939.64)
' One Million Eight Hundred Seventy-Three Thousand Nine Hundred Thirty-Nine and 64/100
'>>> NumToString(-2137864217.58)
' Negative Two Billion One Hundred Thirty-Seven Million Eight Hundred Sixty-Four Thousand Two Hundred Seventeen and 58/100
'>>> NumToString("2147483647.99")
' Two Billion One Hundred Forty-Seven Million Four Hundred Eighty-Three Thousand Six Hundred Forty-Seven and 99/100
'---------------------------------------------------------------------------------------
'
Function NumToString(Val As Variant, Optional ShowNo100 As Boolean = True) As String
Static BeenHere As Boolean, Units As Variant, Teens As Variant, Tens As Variant, Illions As Variant
Dim Hyphen As String, NumCommas As Integer, Mult As Long

    If Not BeenHere Then
        Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
        Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
        Tens = Array("Ten", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
        Illions = Array("", "Thousand", "Million", "Billion")
    End If
    BeenHere = True

    If IsNull(Val) Then
        NumToString = "Null"
    ElseIf Not IsNumeric(Val) Then
        NumToString = "N/A"
    ElseIf Val < 0 Then
        NumToString = "Negative " & NumToString(-Val, False)
    ElseIf Int(Val) <> Val Then
        NumToString = Replace(NumToString(Int(Val), True), " and NO/100", "") & " and " & _
                      VBA.Round((Val - Int(Val)) * 100) & "/100"
    ElseIf Val >= 1000 Then
        NumCommas = Int(VBA.Log(Val) / VBA.Log(1000))
        Mult = 10 ^ (3 * NumCommas)
        If Val Mod Mult <> 0 Then Hyphen = " "
        NumToString = NumToString(Int(Val / Mult), False) & " " & Illions(NumCommas) & Hyphen & _
                      NumToString(Val Mod Mult, False)
    ElseIf Val >= 0 And Val <= 9 Then
        NumToString = Units(Val)
    ElseIf Val >= 10 And Val <= 19 Then
        NumToString = Teens(Val - 10)
    ElseIf Val >= 20 And Val <= 99 Then
        If (Val \ 10) <> (Val / 10) Then Hyphen = "-"
        NumToString = Tens((Val \ 10) - 1) & Hyphen & NumToString(Val Mod 10, False)
    ElseIf Val >= 100 And Val <= 999 Then
        If (Val \ 100) <> (Val / 100) Then Hyphen = " "
        NumToString = Units(Val \ 100) & " Hundred" & Hyphen & NumToString(Val Mod 100, False)
    End If

    If ShowNo100 Then
        If Len(Replace(NumToString, "Zero", "")) = 0 Then NumToString = "Zero"
        If InStr(NumToString, " and ") = 0 Then NumToString = NumToString & " and NO/100"
    End If
End Function

Image by Shutterbug75 from Pixabay