| 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
|