Convert Digit
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Convert Digit as PDF for free.
More details
- Words: 739
- Pages: 4
Summary : This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56: One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents The Function Wizard can also be used to enter a custom function in a worksheet. To use the Function Wizard, follow these steps: 1. Click the Function Wizard button, and select User Defined under Function Category. 2. Select ConvertCurrencyToEnglish, and enter your number or cell reference. 3. Click Finish To Create the Sample Functions 1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module. 2. Type the following code into the module sheet. Function ConvertCurrencyToEnglish (ByVal MyNumber) Dim Temp Dim Rupees, Paise Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Lac " Place(4) = " Carore " Place(5) = " Arab " ' Convert MyCurrency to a string, trimming extra spaces. MyCurrency = Trim(Str(MyNumber)) ' Find decimal place. DecimalPlace = InStr(MyCurrency, ".") ' If we find decimal place... If DecimalPlace > 0 Then ' Convert paise Temp = Left(Mid(MyCurrency, DecimalPlace + 1) & "00", 2) paise = ConvertTens(Temp) ' Strip off paise from remainder to convert. MyCurrency = Trim(Left(MyCurrency, DecimalPlace - 1)) End If
Count = 1 Do While MyCurrency <> "" ' Convert last 3 digits of MyCurrency to English rupees. Temp = ConvertHundreds(Right(MyCurrency, 3)) If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyCurrency) > 3 Then ' Remove last 3 converted digits from MyCurrency. MyCurrency = Left(MyCurrency, Len(MyCurrency) - 3) Else MyCurrency = "" End If Count = Count + 1 Loop ' Clean up rupees. Select Case Rupees Case "" Rupees = "No Rupees" Case "One" Rupees = "One Rupees" Case Else Rupees = Rupees & " Rupees" End Select ' Clean up paise. Select Case Paise Case "" Paise = " And No Paise" Case "One" Paise = " And One Paise" Case Else Paise = " And " & Paise & " Paise" End Select ConvertCurrencyToEnglish = Rupees & Paise End Function
Private Function ConvertHundreds (ByVal MyCurrency) Dim Result As String ' Exit if there is nothing to convert. If Val(MyCurrency) = 0 Then Exit Function ' Append leading zeros to number. MyCurrency = Right("000" & MyCurrency, 3) ' Do we have a hundreds place digit to convert? If Left(MyCurrency, 1) <> "0" Then Result = ConvertDigit(Left(MyCurrency, 1)) & " Hundred "
End If ' Do we have a tens place digit to convert? If Mid(MyCurrency, 2, 1) <> "0" Then Result = Result & ConvertTens(Mid(MyCurrency, 2)) Else ' If not, then convert the ones place digit. Result = Result & ConvertDigit(Mid(MyCurrency, 3)) End If ConvertHundreds = Trim(Result) End Function
Private Function ConvertTens (ByVal MyTens) Dim Result As String ' Is value between 10 and 19? If Val(Left(MyTens, 1)) = 1 Then Select Case Val(MyTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' .. otherwise it's between 20 and 99. Select Case Val(Left(MyTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select ' Convert ones place digit. Result = Result & ConvertDigit(Right(MyTens, 1)) End If ConvertTens = Result
End Function
Private Function ConvertDigit (ByVal MyDigit) Select Case Val(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
Count = 1 Do While MyCurrency <> "" ' Convert last 3 digits of MyCurrency to English rupees. Temp = ConvertHundreds(Right(MyCurrency, 3)) If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyCurrency) > 3 Then ' Remove last 3 converted digits from MyCurrency. MyCurrency = Left(MyCurrency, Len(MyCurrency) - 3) Else MyCurrency = "" End If Count = Count + 1 Loop ' Clean up rupees. Select Case Rupees Case "" Rupees = "No Rupees" Case "One" Rupees = "One Rupees" Case Else Rupees = Rupees & " Rupees" End Select ' Clean up paise. Select Case Paise Case "" Paise = " And No Paise" Case "One" Paise = " And One Paise" Case Else Paise = " And " & Paise & " Paise" End Select ConvertCurrencyToEnglish = Rupees & Paise End Function
Private Function ConvertHundreds (ByVal MyCurrency) Dim Result As String ' Exit if there is nothing to convert. If Val(MyCurrency) = 0 Then Exit Function ' Append leading zeros to number. MyCurrency = Right("000" & MyCurrency, 3) ' Do we have a hundreds place digit to convert? If Left(MyCurrency, 1) <> "0" Then Result = ConvertDigit(Left(MyCurrency, 1)) & " Hundred "
End If ' Do we have a tens place digit to convert? If Mid(MyCurrency, 2, 1) <> "0" Then Result = Result & ConvertTens(Mid(MyCurrency, 2)) Else ' If not, then convert the ones place digit. Result = Result & ConvertDigit(Mid(MyCurrency, 3)) End If ConvertHundreds = Trim(Result) End Function
Private Function ConvertTens (ByVal MyTens) Dim Result As String ' Is value between 10 and 19? If Val(Left(MyTens, 1)) = 1 Then Select Case Val(MyTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' .. otherwise it's between 20 and 99. Select Case Val(Left(MyTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select ' Convert ones place digit. Result = Result & ConvertDigit(Right(MyTens, 1)) End If ConvertTens = Result
End Function
Private Function ConvertDigit (ByVal MyDigit) Select Case Val(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
