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

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.

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

• 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. – FreeMan Jul 12 '19 at 13:08
• @FreeMan Agree. Thanks. The same would apply to the variable "t". – Mohsen Alyafei Jul 12 '19 at 13:36
• 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. – FreeMan Jul 12 '19 at 14:14
• @FreeMan Thanks. I have updated the code (additional separate edit) with more meaningfull var names for better understanding. – Mohsen Alyafei Jul 12 '19 at 16:55
• 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 – FreeMan Jul 12 '19 at 17:00

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.

• 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 Jul 14 '19 at 10:30
• The method can be easily adapted for Asian number conversion as they use Twins instead of Triplets above numbers of 99 thousand. – Mohsen Alyafei Jul 14 '19 at 10:32
• @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). – Mathieu Guindon Jul 17 '19 at 17:19
• @MathieuGuindon Thanks. Great App, was looking for such an app for VBA. Just downloaded it and experimenting :-). – Mohsen Alyafei Jul 18 '19 at 16:29

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.

• Thanks for the alternative method and your suggestions. – Mohsen Alyafei Jul 14 '19 at 9:12
• @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. – TinMan Jul 14 '19 at 10:04
• (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. – Roland Illig Jul 14 '19 at 13:20
• @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/… – Mohsen Alyafei Jul 14 '19 at 13:49
• @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. – Roland Illig Jul 14 '19 at 14:31