Create the KWD function to convert numbers to words (Kuwaiti Dinar)
1. Use
the keyboard shortcut, Alt + F11 to open the
Visual Basic Editor (VBE).
2. Click the Insert tab,
and click Module.
3. Copy
the following lines of code.
Option Explicit
'Main Function
Function KWD(ByVal MyNumber)
Dim Dinar, Fils, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber =
Trim(Str(MyNumber))
DecimalPlace =
InStr(MyNumber, ".")
If DecimalPlace
> 0 Then
Fils =
GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & "000",
3))
MyNumber =
Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp =
GetHundreds(Right(MyNumber, 3))
If Temp <>
"" Then Dinar = Temp & Place(Count) & Dinar
If Len(MyNumber)
> 3 Then
MyNumber
= Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber
= ""
End If
Count = Count +
1
Loop
Select Case Dinar
Case
""
Dinar
= " Kuwaiti Dinar Zero"
Case
"One"
Dinar
= " Kuwaiti Dinar One"
Case Else
Dinar
= " Kuwaiti Dinar " & Dinar
End Select
Select Case Fils
Case
""
Fils
= ""
Case
"One"
Fils
= " and Fils One"
Case
Else
Fils
= " and " & " Fils " & Fils
End Select
KWD = Dinar & Fils & "
Only"
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" &
MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <>
"0" Then
Result =
GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <>
"0" Then
Result = Result
& GetTens(Mid(MyNumber, 2))
Else
Result = Result
& GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result =
"" '
Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then '
If value between 10-19...
Select Case
Val(TensText)
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 '
If value between 20-99...
Select Case
Val(Left(TensText, 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
Result = Result
& GetDigit (Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit
= "One"
Case 2: GetDigit
= "Two"
Case 3: GetDigit
= "Three"
Case 4: GetDigit
= "Four"
Case 5: GetDigit
= "Five"
Case 6: GetDigit
= "Six"
Case 7: GetDigit
= "Seven"
Case 8: GetDigit
= "Eight"
Case 9: GetDigit
= "Nine"
Case Else:
GetDigit = ""
End Select
End Function
4. Paste the lines of code into
the Module1 (Code) box.
5. Press Alt
+ Q to return to Excel. The KWD function is now ready
to use.
Use the KWD function in individual cells
1. Type
the formula =KWD(A1) into the cell where you want to display
a written number, where A1 is the cell containing the number you want to
convert. You can also manually type the value like =KWD(22.500).
2. Press Enter to
confirm the formula.
Save your KWD function workbook
Excel cannot save a workbook with macro functions in the
standard macro-free workbook format (.xlsx). If you click File > Save.
A VB project dialog box opens. Click No.
You can save your file as an Excel Macro-Enabled
Workbook (.xlsm) to keep your file in its current format.
1. Click File > Save
As.
2. Click
the Save as type drop-down menu, and select Excel
Macro-Enabled Workbook.
3. Click Save.
0 Comments