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
```

• FreeMan: Right off the bat, Dim Ones(), Tens(), Scle(), Sep, NumAll As String produces 3 Variant arrays, one Variant and one String variable. The code will, of course work with Variants, but if they're all expected to be String, then skipping the implicit conversion of Variant to String will improve performance and readability.
• Mohsen Alyafei: @FreeMan Agree. Thanks. The same would apply to the variable "t".
• FreeMan: Actually, T is declared String. Triplet, L, i, j however are all Variant. I'd strongly recommend some better names for those single letter variables - what is T, anyway? i is OK as a loop index (pretty darn conventional), but L?, j? those are meaningless. N1 and N2 are pretty... vague as well.
• Mohsen Alyafei: @FreeMan Thanks. I have updated the code (additional separate edit) with more meaningfull var names for better understanding.
• FreeMan: One of the rules of CR is to not edit your code based upon the reviews. Having posted it as a separate section might be ok

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.

• Mohsen Alyafei: thanks for your suggestions and advice. I started the inner part as function delaing with numbers from 000 to 999 only (i.e. one Triplet) (see this article codereview.stackexchange.com/questions/223615/…) and the principal code liner to create the string Triplets was a separate article too here: stackoverflow.com/questions/56876655/…. Each element was tested separately to ensure accuracy. Thanks again. The SLST metod spec (as I call it) is great.
• Mohsen Alyafei: The method can be easily adapted for Asian number conversion as they use Twins instead of Triplets above numbers of 99 thousand.
• Mathieu Guindon: @MohsenAlyafei consider using an actual unit testing framework for the tests. Rubberduck has a boilerplate-free one that's fairly simple to use (full disclaimer: I own that website; Rubberduck is free and open-source).
• Mohsen Alyafei: @MathieuGuindon Thanks. Great App, was looking for such an app for VBA. Just downloaded it and experimenting :-).

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.

• Mohsen Alyafei: Thanks for the alternative method and your suggestions.
• TinMan: @MohsenAlyafei it was may pleasure. Good job by the way. I originally started to write a recursive function but noticed gave up after I reread your specifications. It was a bit of a pain to do from scratch anyhow.
• Roland Illig: (Len(Value) * 2) Mod 3 is really bad style. Calculating the number of missing digits has nothing to do with multiplying by 2. It is only by coincidence that this formula has the indented results. This is codereview, not codegolf.
• Mohsen Alyafei: @RolandIllig the formula is not coincidental it is a formual that functions for any Multiple and is (I believe very sound) It is : (Len (value) * (Multiple-1)) Mod Multiple), please see this article for more details with answer at the end of the article: stackoverflow.com/questions/56876655/…
• Roland Illig: @MohsenAlyafei ah, ok, my bad. I did not read the 2 as n-1 but as being 2 for all cases, which would not have worked. Thanks for the explanation.