I need to know from this workgroup or others who has been
able to translate and practically apply in the real world.
And, was there any late versions having new functions to
display such text?
And in MS Access, too?
Is there any particular group interested in this function?
Rudy Escoto
The VBA macros above can be used in Access or in Excel.
Related information: (in my strings.htm)
To protect numbers format as: _($**#,##0.00_);_($**(#,##0.00);_($**"-"??_);_(@_)
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Rudy Escoto" <noSpam~ree12345@hotmaiDOTcom> wrote in message news:9bf601c11a98$bf634630$9ee62ecf@tkmsftngxa05...
Below are two functions, used like
=Dollars(111.11, TRUE)
to return
One Hundred Eleven Dollars And 11/100
or like
=Dollars(111.11, FALSE)
or
=Dollars(A1, FALSE)
to return
One Hundred Eleven And 11/100
It will work up to Trillions.
HTH,
Bernie
Function Dollars(n As Double, useword As Boolean) As String
Dim myLength As Integer
Dim i As Integer
Dim myNum As Integer
Dim Remainder As Integer
Dollars = ""
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
Dollars = Dollars & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", " trillion
")
End If
Next i
Dollars = Dollars & IIf(useword, " Dollars", "") & " and " &
Format(Remainder, "00") & "/100"
Dollars = Application.WorksheetFunction.Proper(Trim(Dollars))
End Function
Function MakeWord(inValue As Integer) As String
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"
End If
hund = n \ 100
If hund > 0 Then
MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
End If
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.WorksheetFunction.Proper(Trim(MakeWord))
End Function
>.
>