• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

ExcelDataPro

Free Excel Pro Templates

  • Glossary
    • Accounting Glossary
    • HR & Payroll Glossary
    • Tax Glossary
  • Templates
    • Accounting
      • Financial Analysis
      • Financial Statement
    • Educational
    • Taxation
      • Federal Income Tax
      • GST
      • UAE VAT
      • UK VAT
    • Human Resources
      • HR & Payroll
      • HR Metrics
    • School Management
    • Social Media
    • Sales-Marketing
    • Personal Finance
    • Other
      • Health & Fitness
  • Calendars
  • Functions
  • FAQ’s
  • Blog
  • About

SpellNumber UAE Dirhams Function In Excel

SpellNumber UAE Dirhams Function In Excel

SpellNumber UAE Dirhams is an Excel function manually through VBA Programming to change a given number in UAE Dirhams to written words. In other words, it converts a numeric value of AED into English words including Fils.

The SpellNumber macro spells the numeric value to words as the name suggests. There is no direct function in Microsoft Excel to perform the above action.

However, as many and many users demanded, Microsoft has created and published the special VBA macro code on their website for SpellNumber with Currency.

For example;

If you want to display $ 2,345.50 as “Dollar Two Thousand Three Hundred Forty-Five and Fifty Cents”, using the Microsoft function you can do that.

Whereas if you want to display AED 2,345.50 to be displayed as ” Two Thousand Three Hundred Forty-Five Dirhams and Fifty Fils ” then we need to use the SpellNumber UAE Dirham Function.

We have edited the same code with some changes for the required result. In this article, we will discuss the whole step by step process on how to create this SpellNumber UAE Dirham Function using the VBA.

You can also check other spellnumber function like SpellNumber Japanese Yen, SpellNumber Great Britain Pound  SpellNumber Without Currency, SpellNumber Saudi Riyals & SpellNumber Indian Rupees.

How To Create SpellNumber UAE Dirhams Function

Step 1: Open an MS Excel workbook.

Step 2: Go To Developer Tab.

Step 3: Click “Visual Basic” under Code.

Step 4: Click on “Module” under the Insert menu as shown below:

SpellNumber UAE Dirhams

Step 4: Copy the below-given code and paste it into the Module sheet.

Code for SpellNumber Function for AED

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

view raw
SpellNumberEDP.vb
hosted with ❤ by GitHub

Step 5: Click on the “Save” button in the top ribbon.  The following message will be displayed: “The following features cannot be saved in macro-free workb0 ook”. Your file now consists of a macro thus you need to Click “No” option.

SpellNumber UAE Dirhams

As soon as you click on “No” the below screen will appear. Select “Save As”.

SpellNumber UAE Dirhams

Choose the desired disk space where you want to save this file and Select the “Save as type” as “Excel macro-enabled workbook”.

SpellNumber function for AED (UAE Dirham) is now saved in your workbook.

Note: Please keep in mind that this function will only be available in this workbook.

If you require the same code in another workbook then you need to paste the code by following the above-given steps.

Furthermore, every time you open this workbook or any other macro-enabled workbook you will get a security warning below the ribbon.

SpellNumber UAE Dirhams

You need to select “Enable this content” and press enter or click “OK”. Otherwise, the code will not function.

SpellNumber UAE Dirhams

How To Use SpellNumber UAE Dirham Function

Insert numerical data in Column A.

In Column B, Call the SpellNumberEDP Function, along with the cell reference and “AED” as the second parameter. This second parameter is not case sensitive thus you can enter either in caps or in small.

SpellNumber UAE Dirham

Applying the function, it will display the result as given below:

SpellNumber UAE Dirham

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.

Filed Under: Microsoft Excel

You are Here: Home / Reader's Question / Microsoft Excel / SpellNumber UAE Dirhams Function In Excel

About Fahim Lashkaria

I love working in Excel and I share functional excel templates at ExcelDataPro. Read more about me here or interact with me on twitter.

Comments

  1. ABDUL says

    September 7, 2019 at 6:50 pm

    NOT WORKING

    Reply
    • Fahim Lashkaria says

      September 12, 2019 at 11:15 am

      You need to save the file as a macro-enabled file. The spell number is specific for the file you add. It will not work in another file unless you do add the code in the new file and save it as a macro-enabled file.

      Reply
    • Syed Ahmed Osaid says

      August 27, 2020 at 5:19 pm

      it works for me maybe you haven’t followed the
      instructions properly

      Reply
  2. Basweer says

    June 21, 2020 at 2:28 pm

    Dear Sir,
    i followed all the steps you mentioned but there is only one problem occuring which is the currency. Dollar comes instead of “AED”. please help

    Reply
    • Fahim Lashkaria says

      June 22, 2020 at 12:36 pm

      Dear Basweer, Please read the ” How To Use SpellNumber UAE Dirham Function ” at the end to know how to use this function.

      Reply
  3. Basweer says

    June 22, 2020 at 1:17 pm

    Yaah sir it worked 100%
    Can you do a little bit more changes for me in the formula for example the current formula for numbers is:
    E.g,(234.00= Two Hundred Thirty Four dirhams and no fils, or 234.75= Two Hundred Thirty Four dirhams and Seventy Five fils).

    But i want to the formation of above numbers something like this:
    E.g,(234.00=Two Hundred and Four Dirhams Only(removing “and no fils” with “Dirhams Only”) or 234.75=Two Hundred Thirty Four dirhams and Seventy Five Fils Only (adding “Only” after fils)

    If will be your kind favour.
    Thank you

    Reply
  4. Jithesh Rajan says

    September 7, 2020 at 3:23 pm

    Dear Sir
    I can’t use this 1 more excel file

    Reply
    • Fahim Lashkaria says

      September 10, 2020 at 9:49 am

      You have to insert in every new file in which you want to use.

      Reply
  5. Maryam says

    October 21, 2020 at 2:30 pm

    Dear Sir,
    I have followed all the steps. But when I type in the formula in the cell and press ENTER key, it tells me there is a ‘Compile Error: Syntax Error’.

    Line 3 of the code is highlighted in yellow, Line 26 text color is changed to red color.

    Reply
    • Fahim Lashkaria says

      October 23, 2020 at 5:27 pm

      The code has been rectified and please follow the instructions given at the end of the article.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2021. Content Licensed Under Creative Commons with Attribution Required

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.