5
\$\begingroup\$

I have come across many spell number functions and have reviewed many VBA code available on the net.

The core function that gets called repeatedly is the function that converts numbers from 001 to 999 as it is the core function for conversion under the English numeral system. I have seen that this core function is sometime unnecessarily split over sub-functions for converting one, tens, and 20s to 90s.

I have developed the following simple VBA function that takes an input as number in a string format from "001" to "999" and returns the output as a string. The function uses the dash "-" for numbers e.g. Forty-Two.

The function is easily convertible to other programming languages.

With your assistance, I am looking to further improve or simplifying the function, if possible.

You may test the function like this:

Debug.Print Do999("123")
Debug.Print Do999("001")
Debug.Print Do999("099")

Thanks in advance for your contribution.

Function Do999(ThreeDigits As String)
'-----------------------------------------
'Converts number string from 001 to 999 to Words
'Uses dash for in-between numbers from 21 to 99 for UK/US English
'Mohsen Alyafei 17 Oct 2018
'On Entry: NumIn MUST be a 3 Chars digit string "001" to "999"
'On Exit : String of number in English words
'-----------------------------------------
Dim Ones(), Tens(), dash As String, h As String, t As String, N1 As Integer, N2 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", " Hundred")

'Next line is optional for English speaking words (UK, US)
If Right(ThreeDigits, 1) <> "0" Then dash = "-"  'dash as per English spelling

'------------Code starts here------------
'Get the hundreds (N1) and tens (N2)

N1 = Left(ThreeDigits, 1): N2 = Right(ThreeDigits, 2)
If N2 > 19 Then t = Tens(Val(Mid(ThreeDigits, 2, 1))) & dash & Ones(Val(Right(ThreeDigits, 1))) Else t = Ones(N2)
Do999 = Trim(IIf(N1 > 0, Ones(N1) & Tens(10), "") & " " & t)

End Function
\$\endgroup\$
  • \$\begingroup\$ I meant programming languages. Sure languages using the Masculine and Feminine such as French and Arabic will need lots of modifications. \$\endgroup\$ – Mohsen Alyafei Jul 6 at 11:45
  • 1
    \$\begingroup\$ The rules for the conversion are ad-hoc, so fundamental simplification isn't likely. One obvious area of improvement is to extend the functionality so that it applies to more than three digits. A minor improvement would be to make the input Variant so that you could pass it either an integer variable containing a 3-digit number or a string variable and have it work in either case without a type mismatch error. \$\endgroup\$ – John Coleman Jul 6 at 12:21
  • \$\begingroup\$ @JohnColeman Thanks John. This function is a core sub-function that will be called many times from a main function to convert vary large numbers. The main function will take care of the dirty work and will always pass a 3 digit number string for conversion. Integer conversion happens at the higher level function. The higher function will deal with any length of number from 0 to a Decillion. \$\endgroup\$ – Mohsen Alyafei Jul 6 at 12:42
3
\$\begingroup\$

Code Formatting

What certainly could be improved is the indentation of your code for better readability:

Function Do999(ThreeDigits As String)
    'Everything after your function header until End Function should be indented
    ' ...
End Function

Same for further conditional code blocks:

N1 = Left(ThreeDigits, 1): N2 = Right(ThreeDigits, 2)
If N2 > 19 Then 
    t = Tens(Val(Mid(ThreeDigits, 2, 1))) & dash & Ones(Val(Right(ThreeDigits, 1))) 
Else 
    t = Ones(N2)
End If

Do999 = Trim(IIf(N1 > 0, Ones(N1) & Tens(10), "") & " " & t)

Function naming

Do999 isn't very clear / self descriptive about what the function does.
Convert0UpTo999ToWords might be a better choice for example.

Number output in words

The function uses the dash "-" for numbers e.g. Forty-Two.

That's not how numbers are naturally written in words. Usually 42 would be written as forty-two.

Avoid the 1st letter of the number words to be capitalized.

\$\endgroup\$
  • \$\begingroup\$ Thanks. Please check this "Names of numbers in English" in Wikipedia. Which says: When writing other numbers between 21 and 99, you must use a hyphen (-). 21: twenty-one 29: twenty-nine 64: sixty-four 99: ninety-nine \$\endgroup\$ – Mohsen Alyafei Jul 6 at 13:29
  • \$\begingroup\$ @MohsenAlyafei OK, the dash seems to be common according this WP article. The capitalized 1st letters not though. I've reflected that in my answer. \$\endgroup\$ – πάντα ῥεῖ Jul 6 at 13:33
  • \$\begingroup\$ Agree. Capitalised 1st letter is not always common, unless sometimes the number is part of a currency conversion, where some accountants prefer it. \$\endgroup\$ – Mohsen Alyafei Jul 6 at 13:37
2
\$\begingroup\$

The word "hundred" doesn't belong in the Tens array:

  • It is the only element in the array that starts with a space
  • It is only accessed at one place in the code
  • It is never accessed from the place in the code where the other elements are accessed

Therefore it is better to either:

  • Declare it as a single string variable
  • Just use the string " hundred" literally, directly in the one place where it is needed

Another strange thing is that the dash variable contains a dash in most cases but sometimes also contains nothing at all. In the latter case its variable name dash doesn't accurately describe what the variable contains. Rename it to separator or sep instead.

\$\endgroup\$
  • \$\begingroup\$ Thanks Roland for your observations and advice. The reason I put the string " hundred" in the array too was because it was the only string that oddly appeared as part of the code. The var "dash" could also be named correctly "hyphen". \$\endgroup\$ – Mohsen Alyafei Jul 6 at 16:10
  • 1
    \$\begingroup\$ It doesn't matter whether it's hyphen or dash. The confusing part is only when it is called that way but contains something entirely different, in this case an empty string. \$\endgroup\$ – Roland Illig Jul 6 at 18:40
  • \$\begingroup\$ True. It contains an empty string "". The Empty String is a valid element in concatenation operation. It is a valid string data type with zero length. It simplifies coding and exists in all programming languages. What code alternative do you suggest? Thanks \$\endgroup\$ – Mohsen Alyafei Jul 6 at 19:05
  • 1
    \$\begingroup\$ The empty string is great. It just shouldn't be stored in a variable whose name is dash or hyphen, since then the name of the variable does not match what the variable contains. \$\endgroup\$ – Roland Illig Jul 6 at 19:41
  • \$\begingroup\$ I did in fact change the var name to "Separator" to avoid confusion as suggested by another commentor above. Please see below updates. \$\endgroup\$ – Mohsen Alyafei Jul 6 at 19:51

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.