Create the QAR function
to convert numbers to words (Qatari Riyal)
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.
'Main Function
Function QAR(ByVal MyNumber)
Dim Riyal,
Dirham, 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
Dirham
= GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & "00",
2))
MyNumber
= Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count =
1
Do
While MyNumber <> ""
Temp
= GetHundreds(Right(MyNumber, 3))
If
Temp <> "" Then Riyal = Temp & Place(Count) & Riyal
If
Len(MyNumber) > 3 Then
MyNumber
= Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber
= ""
End
If
Count
= Count + 1
Loop
Select
Case Riyal
Case
""
Riyal
= " Qatari Riyal Zero"
Case
"One"
Riyal
= " Qatari Riyal One"
Case
Else
Riyal
= " Qatari Riyal " & Riyal
End
Select
Select
Case Dirham
Case
""
Dirham
= ""
Case
"One"
Dirham
= " and Dirham One"
Case
Else
Dirham
= " and " & " Dirham " & Dirham
End
Select
QAR = Riyal
& Dirham & " 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 QAR function is now ready
to use.
Use the QAR function
in individual cells
1. Type
the formula =QAR(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 =QAR(22.50).
2. Press Enter to
confirm the formula.
Save your QAR 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