Khalil,
Pardon my jumping in - I saw my name..... ;-)
Try the code below. This will reverse the ones and tens for the numbers
above 20 - If you need to have, for example, Five and Ten instead of
Fifteen, change the strings in the array declaration:
unitWord = Array("", "one", "two", "three", "four", _
"five", "six", "seven", "eight", _
"nine", "ten", "eleven", "twelve", _
"thirteen", "fourteen", "fifteen", _
"sixteen", "seventeen", "eighteen", "nineteen")
For exampleL
unitWord = Array("", "one", "two", "three", "four", _
"five", "six", "seven", "eight", _
"nine", "ten", "one and ten", "two and ten", _
"three and ten", "four and ten", "five and ten", _
"six and ten", "seven and ten", "eight and ten", "nine
and ten")
mmmm - now I understand where the 'teen' words were derived....
HTH,
Bernie
MS Excel MVP
Function SpellNumber(ByVal n As Double, _
Optional ByVal useword As Boolean = True, _
Optional ByVal ccy As String = "Dollars", _
Optional ByVal cents As String = "", _
Optional ByVal join As String = " And", _
Optional ByVal fraction As Boolean = False) As String
Dim myLength As Long
Dim i As Long
Dim myNum As Long
Dim Remainder As Long
SpellNumber = ""
Remainder = Round(100 * (n - Int(n)), 0)
myLength = Int(Application.Log10(n) / 3)
For i = myLength To 0 Step -1
myNum = Int(n / 10 ^ (i * 3))
n = n - myNum * 10 ^ (i * 3)
If myNum > 0 Then
SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", "
trillion")
End If
Next i
SpellNumber = SpellNumber & IIf(useword, " " & ccy, "") & _
IIf(Remainder > 0, join & " " & Format(Remainder, "00"),
" Only") & _
IIf(fraction, "/100", "") & " " & cents
SpellNumber = Application.Proper(Trim(SpellNumber))
End Function
Function MakeWord(ByVal inValue As Long) As String
Dim unitWord, tenWord
Dim n As Long
Dim unit As Long, ten As Long, hund As Long
unitWord = Array("", "one", "two", "three", "four", _
"five", "six", "seven", "eight", _
"nine", "ten", "eleven", "twelve", _
"thirteen", "fourteen", "fifteen", _
"sixteen", "seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", _
"fifty", "sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = inValue
If n = 0 Then MakeWord = "zero"
hund = n \ 100
If hund > 0 Then MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
unit = n - ten * 10
MakeWord = MakeWord & unitWord(unit) & " "
MakeWord = Trim(MakeWord & tenWord(ten))
End If
MakeWord = Application.Proper(Trim(MakeWord))
End Function
