Very Simple Spell (Number to Words) 001 to 999 in VBA

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Another minor tweak - if you speak English rather than American change the last line to:

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