excel vba number to words

excel vba number to words

Function NumberToWords(ByVal MyNumber As Double) As String

    Dim DecimalPlace, Count
    Dim Hundreds, Words, FirstDigit, TensDigit As String
    
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    
    MyNumber = Trim(Str(MyNumber))
    
    DecimalPlace = InStr(MyNumber, ".")
    
    If DecimalPlace > 0 Then
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If
    
    Count = 1
    
    Do While MyNumber <> ""
        Hundreds = ""
        Select Case Right(MyNumber, 3)
            Case Is < 100
                TensDigit = ""
                FirstDigit = ConvertDigit(Val(Right(MyNumber, 1)))
                If Val(Right(MyNumber, 2)) < 20 Then
                    TensDigit = ConvertTen(Val(Right(MyNumber, 2)))
                    FirstDigit = ""
                Else
                    TensDigit = ConvertTen(Val(Mid(MyNumber, Len(MyNumber) - 1, 2)))
                    FirstDigit = ConvertDigit(Val(Right(MyNumber, 1)))
                End If
            Case Else
                Hundreds = ConvertDigit(Val(Right(MyNumber, 3) \ 100)) & " Hundred "
                TensDigit = ConvertTen(Val(Mid(MyNumber, Len(MyNumber) - 1, 2)))
                FirstDigit = ConvertDigit(Val(Right(MyNumber, 1)))
        End Select
        
        If Len(MyNumber) > 3 Then
            If Right(MyNumber, 3) <> "000" Then
                Words = Hundreds & TensDigit & FirstDigit & Place(Count) & Words
            End If
        Else
            Words = Hundreds & TensDigit & FirstDigit & Words
        End If
        
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Count = Count + 1
    Loop
    
    NumberToWords = Words
    
End Function

Function ConvertDigit(ByVal MyDigit) As String

    Select Case MyDigit
        Case 1: ConvertDigit = "One"
        Case 2: ConvertDigit = "Two"
        Case 3: ConvertDigit = "Three"
        Case 4: ConvertDigit = "Four"
        Case 5: ConvertDigit = "Five"
        Case 6: ConvertDigit = "Six"
        Case 7: ConvertDigit = "Seven"
        Case 8: ConvertDigit = "Eight"
        Case 9: ConvertDigit = "Nine"
        Case Else: ConvertDigit = ""
    End Select
    
End Function

Function ConvertTen(ByVal MyTens) As String

    Select Case MyTens
        Case 10: ConvertTen = "Ten"
        Case 11: ConvertTen = "Eleven"
        Case 12: ConvertTen = "Twelve"
        Case 13: ConvertTen = "Thirteen"
        Case 14: ConvertTen = "Fourteen"
        Case 15: ConvertTen = "Fifteen"
        Case 16: ConvertTen = "Sixteen"
        Case 17: ConvertTen = "Seventeen"
        Case 18: ConvertTen = "Eighteen"
        Case 19: ConvertTen = "Nineteen"
        Case Else
            Select Case MyTens \ 10
                Case 2: ConvertTen = "Twenty"
                Case 3: ConvertTen = "Thirty"
                Case 4: ConvertTen = "Forty"
                Case 5: ConvertTen = "Fifty"
                Case 6: ConvertTen = "Sixty"
                Case 7: ConvertTen = "Seventy"
                Case 8: ConvertTen = "Eighty"
                Case 9: ConvertTen = "Ninety"
                Case Else: ConvertTen = ""
            End Select
    End Select
End Function