Option Explicit ' ************************************************** ' Description : Module that will transform numbers into ' words ' Author : Ulysses R. Gotera ' Email : urgotera@gmail.com ' Country : Philippines ' Date Created : Sunday, February 19, 2006 ' Date Modified: <> ' Note : This program assumes that the number you are ' going to input is in this format 999999999999.99 ' (up to 999 billion) ' ' This program will not work if a dot is used instead ' of a comma in seperating ones, hundreds, thousands, and millions. ' Some coutries use a dot instead of a comma. ' ' Sample Usage: ' strVariable = udf_strNum2Words(100.50,"Pesos ", "Centevo(s)") ' Debug.Print strVariable ' ' This will result in: ' One Hundred Pesos and Fifty Centevo(s) ' ' ' strVariable = udf_strNum2Words(8788.69,"Dollars ", "Cents") ' ' This will result in: ' Eight Thousand Seven Hundred Eighty Eight Dollars and Sixty Nine Cents ' ' ' strVariable = udf_strNum2Words(88) ' ' This will result in: ' Eighty Eight ' **************************************************

Public Function udf_strNum2Words(ByVal a_dblNumbers As Double, _ Optional ByVal a_strCurSuffix As String = "", _ Optional ByVal a_strDecSuffix As String = "") As String ' Author: Ulysses R. Gotera ' This is the entry function. ' Notice that I deliberately made the ' parameter a double to able to prevent ' alpha numeric characters' ' On Error GoTo ErrHandler Dim strNumbers As String ' ' This will produce a 999,999,999,999.99 format strNumbers = Format$(a_dblNumbers, "Standard") udf_strNum2Words = udf_strProcessWords(strNumbers, _ a_strCurSuffix, a_strDecSuffix) ' ErrHandler: If Err.Number <> 0 Then MsgBox Err.Description, vbInformation, "udf_strNum2Words" End If End Function Public Function udf_strProcessWords(ByVal a_strNumbers As String, _ ByVal a_strCurSuffix As String, _ ByVal a_strDecSuffix As String) As String ' Author: Ulysses R. Gotera ' Make numbers into words On Error GoTo ErrHandler Dim strBuffer As String, bytPos As Byte, _ blnFound As Boolean, bytCommaCntr As Byte, _ bytLen As Byte, bytStart As Byte, strDeciPart As String, _ strNumber As String, strBuffer2 As String ' ' strNumber = a_strNumbers bytPos = InStr(1, strNumber, ".") ' ' Process the decimal part If bytPos > 0 Then strDeciPart = Mid$(strNumber, bytPos + 1, 2) strDeciPart = udf_strProcessHundreds(strDeciPart) If strDeciPart <> "" Then strDeciPart = "and " & strDeciPart End If ' strNumber = Left$(strNumber, bytPos - 1) End If ' blnFound = True: bytPos = 1 Do While blnFound bytPos = InStr(bytPos, strNumber, ",") If bytPos = 0 Then blnFound = False Else bytCommaCntr = bytCommaCntr + 1 bytPos = bytPos + 1 End If Loop ' ' Select Case bytCommaCntr Case 0 ' Hundreds bytLen = Len(strNumber) strBuffer = udf_strProcessHundreds(Mid$(strNumber, 1, bytLen)) Case 1 ' Thousands bytPos = InStr(1, strNumber, ",") strBuffer = udf_strProcessHundreds(Mid$(strNumber, 1, bytPos - 1)) & _ "Thousand " strBuffer = strBuffer & udf_strProcessHundreds(Right$(strNumber, 3)) Case 2 ' Millions bytPos = InStr(1, strNumber, ",") strBuffer = udf_strProcessHundreds(Mid$(strNumber, 1, bytPos - 1)) & _ "Million " ' bytStart = bytPos + 1 bytPos = InStr(bytStart, strNumber, ",") strBuffer2 = udf_strProcessHundreds(Mid$(strNumber, bytStart, 3)) strBuffer = strBuffer & IIf(strBuffer2 = "", "", strBuffer2 & "Thousand ") ' strBuffer = strBuffer & udf_strProcessHundreds(Right$(strNumber, 3)) Case 3 ' Billions bytPos = InStr(1, strNumber, ",") strBuffer = udf_strProcessHundreds(Mid$(strNumber, 1, bytPos - 1)) & _ "Billion " ' bytStart = bytPos + 1 bytPos = InStr(bytStart, strNumber, ",") strBuffer2 = udf_strProcessHundreds(Mid$(strNumber, bytStart, 3)) strBuffer = strBuffer & IIf(strBuffer2 = "", "", strBuffer2 & "Million ") ' bytStart = bytPos + 1 bytPos = InStr(bytStart, strNumber, ",") strBuffer2 = udf_strProcessHundreds(Mid$(strNumber, bytStart, 3)) strBuffer = strBuffer & IIf(strBuffer2 = "", "", strBuffer2 & "Thousand ") ' strBuffer = strBuffer & udf_strProcessHundreds(Right$(strNumber, 3)) End Select ' udf_strProcessWords = strBuffer & a_strCurSuffix & IIf(strDeciPart = "", "", _ strDeciPart & a_strDecSuffix) ' ErrHandler: If Err.Number <> 0 Then MsgBox Err.Description, vbInformation, "udf_strProcessWords" End If End Function Public Function udf_strProcessHundreds(ByVal a_strNumber As String) As String ' Author: Ulysses R. Gotera ' Process 100s ' Dim intNumber As Integer, strBuffer As String, _ strNumber As String ' intNumber = CInt(a_strNumber) strNumber = CStr(intNumber) ' If intNumber < 100 Then udf_strProcessHundreds = udf_strProcessTens(strNumber) Exit Function Else strBuffer = udf_strProcessTens(Right$(a_strNumber, 2)) End If ' ' udf_strProcessHundreds = udf_strProcessOnes(Left$(a_strNumber, 1)) & "Hundred " _ & strBuffer End Function Public Function udf_strProcessTens(ByVal a_strNumber As String) As String ' Author: Ulysses R. Gotera ' Process 10s Dim intNumber As Integer, strBuffer As String ' intNumber = CInt(a_strNumber) ' ' If intNumber < 10 Then udf_strProcessTens = udf_strProcessOnes(a_strNumber) Exit Function End If ' ' Select Case intNumber Case 0 udf_strProcessTens = "" Exit Function Case 10 udf_strProcessTens = "Ten " Exit Function Case 11 udf_strProcessTens = "Eleven " Exit Function Case 12 udf_strProcessTens = "Twelve " Exit Function Case 13 udf_strProcessTens = "Thirteen " Exit Function Case 14 udf_strProcessTens = "Fourteen " Exit Function Case 15 udf_strProcessTens = "Fifteen " Exit Function Case 16 udf_strProcessTens = "Sixteen " Exit Function Case 17 udf_strProcessTens = "Seventeen " Exit Function Case 18 udf_strProcessTens = "Eighteen " Exit Function Case 19 udf_strProcessTens = "Nineteen " Exit Function End Select ' ' intNumber = CInt(Left$(a_strNumber, 1) & "0") Select Case intNumber Case 20 strBuffer = "Twenty " Case 30 strBuffer = "Thirty " Case 40 strBuffer = "Forty " Case 50 strBuffer = "Fifty " Case 60 strBuffer = "Sixty " Case 70 strBuffer = "Seventy " Case 80 strBuffer = "Eighty " Case 90 strBuffer = "Ninety " End Select ' udf_strProcessTens = strBuffer & udf_strProcessOnes(Right$(a_strNumber, 1)) ' End Function Public Function udf_strProcessOnes(ByVal a_strNumber) As String ' Author: Ulysses R. Gotera ' Process 1's Dim intNumber As Integer ' intNumber = CInt(a_strNumber) ' ' If intNumber < 10 Then Select Case intNumber Case 0 udf_strProcessOnes = "" Case 1 udf_strProcessOnes = "One " Case 2 udf_strProcessOnes = "Two " Case 3 udf_strProcessOnes = "Three " Case 4 udf_strProcessOnes = "Four " Case 5 udf_strProcessOnes = "Five " Case 6 udf_strProcessOnes = "Six " Case 7 udf_strProcessOnes = "Seven " Case 8 udf_strProcessOnes = "Eight " Case 9 udf_strProcessOnes = "Nine " End Select End If ' End Function