Wednesday, December 28, 2011

Spell you Number in Words






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
        MakeWord = MakeWord & tenWord(ten) & " "
        unit = n - ten * 10
        MakeWord = Trim(MakeWord & unitWord(unit))
    End If
    MakeWord = Application.Proper(Trim(MakeWord))


End Function

No comments:

Post a Comment

Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.