Simple Number to Words using a Single Loop String Triplets in VBA

Question

As an update (conversion) to my article “Simple Number to Words using a Single Loop String Triplets in JavaScript” in JavaScript, I have converted the code to work as a VBA function using the same method of "Single Loop String Triplets".

The explanation of how it works is detailed in the above reference article with images and examples.

The function is for unsigned integers. But may be called twice for whole and fractional parts after a number split at the decimal point. Also currency and sub-currency words could be added easily if a whole/fractional split is made.

It is not intended for the function to check for bad inputs, negative numbers, decimals, etc. as this could be left to a another higher function that will call this function, therefore the following are not accounted for simplicity:

- No checks for negative numbers.

- No checks for non-number (NaN) strings/data.

- No checks or conversion for exponential notations.

However, large numbers can be passed as a String if necessary.

The “scle” Array may be increased by adding additional scales above “Decillion”.

Examples:

Debug.Print NumToWordsUnsignedInt(777112999)

'Output:
'Seven Hundred Seventy-Seven Million One Hundred Twelve Thousand Nine Hundred Ninety-Nine

Debug.Print NumToWordsUnsignedInt(“222111333444555666777888999111222333”)

'Output:
'Two Hundred Twenty-Two Decillion One Hundred Eleven Nonillion Three Hundred Thirty-Three Octillion Four Hundred Forty-Four Septillion Five Hundred Fifty-Five Sextillion Six Hundred Sixty-Six Quintillion Seven Hundred Seventy-Seven Quadrillion Eight Hundred Eighty-Eight Trillion Nine Hundred Ninety-Nine Billion One Hundred Eleven Million Two Hundred Twenty-Two Thousand Three Hundred Thirty-Three

I would like the code to be reviewed for any bugs, optimization, or improvements. I am sure there is room for improvements and corrections.

Thanks in advance.

Mohsen Alyafei

Function NumToWordsUnsignedInt(ByVal NumIn As String)
'-------------------------------------------------------------
'Convert Unsigned Integer Number to English Words (US System)
'Using a Single Loop String Triplets (SLST) Method
'Mohsen Alyafei 10 July 2019
'Call it separately for a whole number and a fraction
'-------------------------------------------------------------
 Dim Ones(), Tens(), Scle(), Sep, NumAll As String, t As String, N1 As Integer, N2 As Integer, Triplet, L, i, j
 Ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
 Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
 Scle = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion", "Septillion", "Octillion", "Nonillion", "Decillion")

 NumIn = String((Len(NumIn) * 2) Mod 3, "0") & NumIn     'Create shortest string Triplets (0 padded)
 L = Len(NumIn) / 3 - 1:  j = 1                          'Get total no. of Triplets and init count into Triplets

 For i = L To 0 Step -1                                  'Loop starting with Most Signifct Triplet (MST)
  Triplet = Mid(NumIn, j, 3)                             'Get a Triplet starting from LH

  If Triplet <> "000" Then                               'Skip empty Triplets
    Sep = IIf(Right(Triplet, 1) <> "0", "-", "")         'Only if hyphen needed for nums 21 to 99
    N1 = Left(Triplet, 1): N2 = Right(Triplet, 2)        'Get Hundreds digit and 2 lowest digits (00 to 99)

    'First Spell the 2 lowest digits in N2
    If N2 > 19 Then t = Tens(Val(Mid(Triplet, 2, 1))) & Sep & Ones(Val(Right(Triplet, 1))) Else t = Ones(N2)

    'Add " hundred" if needed, Create number with scale, and join the Triplet scales to previous
    NumAll = NumAll & Trim(IIf(N1 > 0, Ones(N1) & " Hundred", "") & " " & t) & " " & Scle(i) & " "

  End If

 j = j + 3                                               'Point to next Triplet position
 Next                                                    'Go for next lower Triplets (move to RH)

NumToWordsUnsignedInt = Trim(NumAll)                      'Return trimming excess spaces
End Function

EDIT 1: Variables Re-Naming for better readability

Variable names updated based on sugegstions.

'-------------------------------------------------------------
Function NumToWordsUnsignedInt(ByVal NumIn As String)
'-------------------------------------------------------------
'Convert Unsigned Integer Number to English Words (US System)
'Using a Single Loop String Triplets (SLST) Method
'Mohsen Alyafei 12 July 2019
'Call it separately for a whole number and a fraction
'-------------------------------------------------------------
 Dim Ones(), tens(), Scle(), Sep, NumAll, W_Tens, Triplet, TotalTriplets, i, TripletPos
 Dim N_Hundrds As Integer, N_Tens As Integer
 Ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
 tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
 Scle = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion", "Septillion", "Octillion", "Nonillion", "Decillion")

 NumIn = String((Len(NumIn) * 2) Mod 3, "0") & NumIn      'Create shortest string Triplets (0 padded)

 TotalTriplets = Len(NumIn) / 3 - 1:  TripletPos = 1      'Get total no. of Triplets and init count into Triplets

 For i = TotalTriplets To 0 Step -1                       'Loop starting with Most Signifct Triplet (MST)
  Triplet = Mid(NumIn, TripletPos, 3)                     'Get a Triplet starting from LH

  If Triplet <> "000" Then                                'Skip empty Triplets
    Sep = IIf(Right(Triplet, 1) <> "0", "-", "")          'Only if hyphen needed for nums 21 to 99
    N_Hundrds = Left(Triplet, 1)                          'Get the Hundreds digit
    N_Tens = Right(Triplet, 2)                            'Get 2 lowest digits (00 to 99)

    'First Spell the 2 lowest digits in N_Tens into W_Tens
    If N_Tens > 19 Then W_Tens = tens(Val(Mid(Triplet, 2, 1))) & Sep & Ones(Val(Right(Triplet, 1))) Else W_Tens = Ones(N_Tens)

    'Add " hundred" if needed, Create number with scale, and join the Triplet scales to previous
    NumAll = NumAll & Trim(IIf(N_Hundrds > 0, Ones(N_Hundrds) & " Hundred", "") & " " & W_Tens) & " " & Scle(i) & " "
  End If

 TripletPos = TripletPos + 3                             'Point to next Triplet position
 Next                                                    'Go for next lower Triplets (move to RH)

NumToWordsUnsignedInt = Trim(NumAll)                     'Return trimming excess spaces
End Function
```

Answer 1

A function should Do One Thing Well.

Yes, this function does have a single stated goal. But accomplishing that breaks down quite naturally into the goals of:

1. SmallNumToWords(n), for input 0 <= n <= 999.
2. NumToWords(n) for non-negative n, which repeatedly breaks out small n and calls the helper function.

Adding several unit tests would be a boon to the casual reader.

Numbers a little bigger than ten are slightly annoying, granted. But since they "escaped" from the one's place, perhaps Small would be a more accurate identifier?

Pat Sajack would be happy to let you use Scale, you don't even have to buy a vowel.

nit: Tens would be more consistent with how you spelled the other identifiers.

Answer 2

A couple of things: - Some the non-variant variables data types are not declared - Avoid using underscores in variable names
- NumToWordsUnsignedInt reads as "Number To Words Unsigned Int" and that just doesn't make sense - I use IIF() quite often myself but it can take away from the readability of the code - Ones should be renamed because it contains numbers 1 to 19 - NumIn, NumAll are very good variable names. Personally, I choose to use the same set of variable names for all my work (such as: value, values, data, result, results, source, target). I see value in my code I know that it is a single scalar value that I am working with. Values and data are arrays. Result is a scalar value that will generally be returned. Results is a array that will generally be returned. These variables names are also part of several patterns that I have memorized. Reusing these patterns speeds up the reading and writing of my code.

Refactored Code

With all the hard work done by the OP, this is how I would write the function:

Function NumbersToWords(ByVal Value As String)
    Dim nHundreds As Long, nOnes As Long, nTens As Long, nScale As Long, n As Long
    Dim result As String
    Dim Small(), Tens(), Scle()
    Small = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    Scle = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion", "Septillion", "Octillion", "Nonillion", "Decillion")


    Value = String((Len(Value) * 2) Mod 3, "0") & Value      'Create shortest string Triplets (0 padded)

    For n = Len(Value) To 1 Step -3
       nOnes = Mid(Value, n - 2, 1)
       nTens = Mid(Value, n - 1, 1)
       nHundreds = Mid(Value, n, 1)
       If nScale > 0 Then result = Scle(nScale) & Space(1) & result

       If nOnes + nTens + nHundreds = 0 Then
            'Skip Empty Triplet
       ElseIf nTens >= 2 And nOnes = 0 Then
            result = Tens(nTens) & Space(1) & result
       ElseIf nTens >= 2 Then
            result = Tens(nTens) & "-" & Small(nOnes) & Space(1) & result
       ElseIf nOnes > 0 Or nTens > 0 Then
            result = Small(nTens * 10 + nOnes) & Space(1) & result
       End If

       If nHundreds > 0 Then result = Small(nHundreds) & " Hundred " & result

       nScale = nScale + 1

    Next

    NumbersToWords = Trim(result)

End Function

Edited per Roland Illig comment.