3
\$\begingroup\$

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

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.

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

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.