SpellNumber Saudi Riyals is an Excel function created through VBA Programming to display a given number in words with Saudi Riyals as currency. It converts a numeric value of SAR into English words including Halalas.
The SpellNumber Function provided by Microsoft spells the numeric value to words as the name suggests.
For example;
Using the Microsoft SpellNumber Function you can display $ 2,345.50 as “Dollar Two Thousand Three Hundred Forty-Five and Fifty Cents”.
To display SAR 2,345.50 as ” Two Thousand Three Hundred Forty-Five Riyals and Fifty Halalas ” you need to use the SpellNumber Saudi Riyal Function.
We have edited the code of Microsoft and made some changes to derive the required result.
You can also check our SpellNumber Without Currency, SpellNumber for UAE Dirhams & SpellNumber Indian Rupees.
Let us discuss the whole step by step process on how to create this SpellNumber Saudi Riyal Function using the VBA.
How To Create SpellNumber Saudi Riyals Function
1: Open an Excel workbook.
2: Click on Developer Tab.
3: Click on “Visual Basic” under the Code Tab.
4: Click on Insert in the File Menu and click on Module as shown below:
A new blank module sheet will open.
5: Copy the below Code into the Module sheet.
Code for SpellNumber Function for SAR (Saudi Arabian Riyal)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Option Explicit | |
| 'Main Function www.ExcelDataPro.com | |
| Function SpellNumberEDP(ByVal MyNumber, Optional MyCurrency As String = "") | |
| Dim Dollars, cents, Temp | |
| Dim DecimalPlace, Count | |
| ReDim Place(9) As String | |
| Place(2) = " Thousand " | |
| Place(3) = " Million " | |
| Place(4) = " Billion " | |
| Place(5) = " Trillion " | |
| ' String representation of amount. | |
| MyNumber = Trim(Str(MyNumber)) | |
| ' Position of decimal place 0 if none. | |
| DecimalPlace = InStr(MyNumber, ".") | |
| ' Convert cents and set MyNumber to dollar amount. | |
| If DecimalPlace > 0 Then | |
| cents = GetTens(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 Dollars = Temp & Place(Count) & Dollars | |
| If Len(MyNumber) > 3 Then | |
| MyNumber = Left(MyNumber, Len(MyNumber) – 3) | |
| Else | |
| MyNumber = "" | |
| End If | |
| Count = Count + 1 | |
| Loop | |
| Dim str_amount, str_amounts | |
| Dim str_cent, str_cents | |
| Select Case UCase(MyCurrency) | |
| Case "SAR" | |
| str_amount = "Riyal" | |
| str_amounts = "Riyals" | |
| str_cent = "Halala" | |
| str_cents = "Halalas" | |
| Case "AED" | |
| str_amount = "Dirham" | |
| str_amounts = "Dirhams" | |
| str_cent = "Fil" | |
| str_cents = "Fils" | |
| Case "GBP" | |
| str_amount = "Pound" | |
| str_amounts = "Pounds" | |
| str_cent = "Penny" | |
| str_cents = "Pence" | |
| Case "EUR" | |
| str_amount = "Euro" | |
| str_amounts = "Euros" | |
| str_cent = "Cent" | |
| str_cents = "Cents" | |
| Case "YEN" | |
| str_amount = "Yen" | |
| str_amounts = "Yens" | |
| str_cent = "Sen" | |
| str_cents = "Sens" | |
| Case Else: | |
| str_amount = "Dollar" | |
| str_amounts = "Dollars" | |
| str_cent = "Cent" | |
| str_cents = "Cents" | |
| End Select | |
| Select Case Dollars | |
| Case "" | |
| Dollars = "No " & str_amounts | |
| Case "One" | |
| Dollars = "One " & str_amount | |
| Case Else | |
| Dollars = Dollars & " " & str_amounts | |
| End Select | |
| Select Case cents | |
| Case "" | |
| cents = " and No " & str_cents | |
| Case "One" | |
| cents = " and One " & str_cent | |
| Case Else | |
| cents = " and " & cents & " " & str_cents | |
| End Select | |
| SpellNumberEDP = Dollars & cents | |
| 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 |
6: Click the “Save” button. The system will display the following message: “The following features cannot be saved in macro-free workbook”.
As your file consists of a macro you need to save the file as a macro-enabled workbook. Click “No” option.
7: Select “Save As”.
8: Choose the desired disk space to save this file. Select the “Save as type” as “Excel macro-enabled workbook”.
SpellNumber Function for SAR (Saudi Arabian Riyal) has been created in your workbook. This function will only be available in this workbook.
You need to follow the same steps and paste the code in another workbook if you want the same function in another workbook.
When you will open any macro-enabled workbook you will get a Security Warning below the Ribbon.
Select “Enable this content” and Click “OK”. If you don’t do this the code will not function.
Let us try using SpellNumber Function for Saudi Riyals which we created here.
Insert different numbers in Column A. In Column B, type the function SpellNumberEDP, enter cell reference and the Currency “SAR” in quote marks.
Using Fill Handle, copy the same formula to Column B. The function will display the numbers as shown below:
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries or suggestions please share in the comment section below. I will be more than happy to assist you.
Leave a Reply