StringToNum

Description:
The function StringToNum will take just about any number written out in english, up to about 920 trillion, or the limit of the Currency variable type, and convert it to a Currency number. StringToNum doesn't make any attempt to validate the number being passed, so it will convert some strings that make no sense.
 
Code:
Public Function StringToNum(ByVal sNumber As String) As Currency
'Example: StringToNum("one hundred twenty-three") = 123

    Dim nLen As Long
    Dim nTemp As Currency
    Dim bNegative As Boolean
    
    sNumber = Replace(sNumber, "-", " ")
    sNumber = Replace(sNumber, ".", " ")
    sNumber = Replace(sNumber, ",", " ")
    Do
        nLen = Len(sNumber)
        sNumber = Replace(sNumber, "  ", " ")
    Loop Until Len(sNumber) = nLen
    sNumber = LCase(Trim(sNumber))

    Dim vNumbers As Variant
    Dim vNumber As Variant
    vNumbers = Split(sNumber, " ")
    For Each vNumber In vNumbers
        Select Case vNumber
            Case "zero"
                'nothing
            Case "negative"
                bNegative = Not bNegative
            Case "a": nTemp = nTemp + 1
            Case "one": nTemp = nTemp + 1
            Case "two": nTemp = nTemp + 2
            Case "three": nTemp = nTemp + 3
            Case "four": nTemp = nTemp + 4
            Case "five": nTemp = nTemp + 5
            Case "six": nTemp = nTemp + 6
            Case "seven": nTemp = nTemp + 7
            Case "eight": nTemp = nTemp + 8
            Case "nine": nTemp = nTemp + 9
            Case "ten": nTemp = nTemp + 10
            Case "eleven": nTemp = nTemp + 11
            Case "twelve": nTemp = nTemp + 12
            Case "thirteen": nTemp = nTemp + 13
            Case "fourteen": nTemp = nTemp + 14
            Case "fifteen": nTemp = nTemp + 15
            Case "sixteen": nTemp = nTemp + 16
            Case "seventeen": nTemp = nTemp + 17
            Case "eighteen": nTemp = nTemp + 18
            Case "nineteen": nTemp = nTemp + 19
            Case "twenty": nTemp = nTemp + 20
            Case "thirty": nTemp = nTemp + 30
            Case "forty": nTemp = nTemp + 40
            Case "fifty": nTemp = nTemp + 50
            Case "sixty": nTemp = nTemp + 60
            Case "seventy": nTemp = nTemp + 70
            Case "eighty": nTemp = nTemp + 80
            Case "ninety": nTemp = nTemp + 90
            Case "trillion"
                nTemp = nTemp * 1000000000000#
                StringToNum = StringToNum + nTemp
                nTemp = 0
            Case "billion"
                nTemp = nTemp * 1000000000
                StringToNum = StringToNum + nTemp
                nTemp = 0
            Case "million"
                nTemp = nTemp * 1000000
                StringToNum = StringToNum + nTemp
                nTemp = 0
            Case "thousand"
                nTemp = nTemp * 1000
                StringToNum = StringToNum + nTemp
                nTemp = 0
            Case "hundred"
                nTemp = nTemp * 100
            Case Else
                If IsNumeric(vNumber) Then
                    nTemp = CCur(vNumber)
                Else
                    Debug.Assert False 'Unknown value
                End If
        End Select
    Next
    
    StringToNum = StringToNum + nTemp
    
    If bNegative Then
        StringToNum = -StringToNum
    End If
    
End Function
 
Sample Usage:
 
Debug.Print StringToNum("Negative Four thousand three hundred twenty-one")