MS Excel Currency converter VBA code

To convert numbers into text, there is no builtin function in MS Excel as of now. Using the below VBA code you can overcome this issue. There are situations like writing a cheque leaf, printing a rent agreement, or writing contract documents that may require printing the transaction amount in the text. Make use of this code to convert numbers into text automatically. 

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