Wednesday, December 7, 2016

MS Excel Currency converter VBA code

Copy paste this VBA code into your Excel VBA eidotr module. Or Insert an empty module and paste this code.
  1. Attribute VB_Name = "Module1"
  2. 'Attribute VB_Name = "Module2"
  3. ' ****  Author          : Krishna S
  4. ' ****  Tittle          : Converting Hindu Arabic Currency(Indian System) to Words
  5. ' ****  Copyright Owner : Krishna S
  6. ' ****  Description     : This utility converts currencies in Indian numbering system to words.
  7. ' ****  Limitations     : Converts only upto 10,00,00,000( Ten Crores)

  8. Function ConvertCurrencyToEnglish(ByVal MyNumber)
  9. Dim Temp
  10.          Dim Rupees, Paise
  11.          Dim DecimalPlace, Count
  12.          ReDim Place(9) As String
  13.          Place(2) = " Thousand "
  14.          Place(3) = " Lac "
  15.          Place(4) = " Core "
  16.       '   Place(5) = " Hundred Core "
  17.          ' Convert MyNumber to a string, trimming extra spaces.
  18.          MyNumber = Trim(Str(MyNumber))
  19.          ' Find decimal place.
  20.          DecimalPlace = InStr(MyNumber, ".")
  21.          ' If we find decimal place...
  22.          If DecimalPlace > 0 Then
  23.             ' Convert Paise
  24.             Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
  25.             Paise = ConvertTens(Temp)
  26.             ' Strip off Paise from remainder to convert.
  27.             MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  28.          End If
  29.          Count = 1
  30.          
  31.          Do While MyNumber <> ""
  32.                  If Count = 1 Then
  33.                 
  34.                    Temp = ConvertHundreds(Right(MyNumber, 3))
  35.                      
  36.                     If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
  37.                     If Len(MyNumber) > 3 Then
  38.                        ' Remove last 3 converted digits from MyNumber.
  39.                        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
  40.                     Else
  41.                        MyNumber = ""
  42.                     End If
  43.                     Count = Count + 1
  44.                  Else
  45.                  ' Convert last 3 digits of MyNumber to English Rupees.
  46.                  If Len(MyNumber) = 1 Then
  47.                  Temp = ConvertDigit(MyNumber)
  48.                  Else
  49.                  Temp = ConvertTens(Right(MyNumber, 2))
  50.                  End If
  51.                     If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
  52.                     If Len(MyNumber) >= 3 Then
  53.                        ' Remove last 3 converted digits from MyNumber.
  54.                        MyNumber = Left(MyNumber, Len(MyNumber) - 2)
  55.                     Else
  56.                        MyNumber = ""
  57.                     End If
  58.                     Count = Count + 1
  59.                     End If
  60.          Loop
  61.          ' Clean up Rupees.
  62.          Select Case Rupees
  63.             Case ""
  64.                Rupees = ""
  65.             Case "One"
  66.                Rupees = "One Rupee"
  67.             Case Else
  68.                Rupees = Rupees & " Rupees"
  69.          End Select
  70.          ' Clean up Paise.
  71.          Select Case Paise
  72.             Case ""
  73.                Paise = ""
  74.             Case "One"
  75.                Paise = " And One Cent"
  76.             Case Else
  77.                Paise = " And " & Paise & " Paise"
  78.          End Select
  79.          ConvertCurrencyToEnglish = Rupees & Paise
  80. End Function
  81. Private Function ConvertHundreds(ByVal MyNumber)
  82. Dim Result As String
  83.          ' Exit if there is nothing to convert.
  84.          If Val(MyNumber) = 0 Then Exit Function
  85.          ' Append leading zeros to number.
  86.          MyNumber = Right("000" & MyNumber, 3)
  87.          ' Do we have a hundreds place digit to convert?
  88.          If Left(MyNumber, 1) <> "0" Then
  89.             Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
  90.          End If
  91.          ' Do we have a tens place digit to convert?
  92.          If Mid(MyNumber, 2, 1) <> "0" Then
  93.             Result = Result & ConvertTens(Mid(MyNumber, 2))
  94.          Else
  95.             ' If not, then convert the ones place digit.
  96.             Result = Result & ConvertDigit(Mid(MyNumber, 3))
  97.          End If
  98.          ConvertHundreds = Trim(Result)
  99. End Function
  100. Private Function ConvertTens(ByVal MyTens)
  101. Dim Result As String
  102.          ' Is value between 10 and 19?
  103.          If Val(Left(MyTens, 1)) = 1 Then
  104.             Select Case Val(MyTens)
  105.                Case 1: Result = "One"
  106.                Case 10: Result = "Ten"
  107.                Case 11: Result = "Eleven"
  108.                Case 12: Result = "Twelve"
  109.                Case 13: Result = "Thirteen"
  110.                Case 14: Result = "Fourteen"
  111.                Case 15: Result = "Fifteen"
  112.                Case 16: Result = "Sixteen"
  113.                Case 17: Result = "Seventeen"
  114.                Case 18: Result = "Eighteen"
  115.                Case 19: Result = "Nineteen"
  116.                Case Else
  117.             End Select
  118.          Else
  119.             ' .. otherwise it's between 20 and 99.
  120.             Select Case Val(Left(MyTens, 1))
  121.                Case 2: Result = "Twenty "
  122.                Case 3: Result = "Thirty "
  123.                Case 4: Result = "Forty "
  124.                Case 5: Result = "Fifty "
  125.                Case 6: Result = "Sixty "
  126.                Case 7: Result = "Seventy "
  127.                Case 8: Result = "Eighty "
  128.                Case 9: Result = "Ninety "
  129.                Case Else
  130.             End Select
  131.             ' Convert ones place digit.
  132.             Result = Result & ConvertDigit(Right(MyTens, 1))
  133.          End If
  134.          ConvertTens = Result
  135. End Function
  136. Private Function ConvertDigit(ByVal MyDigit)
  137. Select Case Val(MyDigit)
  138.             Case 1: ConvertDigit = "One"
  139.             Case 2: ConvertDigit = "Two"
  140.             Case 3: ConvertDigit = "Three"
  141.             Case 4: ConvertDigit = "Four"
  142.             Case 5: ConvertDigit = "Five"
  143.             Case 6: ConvertDigit = "Six"
  144.             Case 7: ConvertDigit = "Seven"
  145.             Case 8: ConvertDigit = "Eight"
  146.             Case 9: ConvertDigit = "Nine"
  147.             Case Else: ConvertDigit = ""
  148.          End Select
  149. End Function

1 comment: