New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Convert Numbers To Words

Free Excel Macros

Convert Numbers To Words

Postby Sisyphus » Thu Sep 20, 2012 5:53 am

This macro is designed to convert amounts – money. Therefore it is limited to 999 billion of integers and 0.01 decimals. Both parameters require some knowledge of VBA to change, the latter more than the former. Other than that the program’s flexibility sets it apart from others that abound on the Internet. It was designed to enable deployment in many variations without any knowledge of VBA.

Flexibility comes in two packages, as it were:-
  1. Setting program parameters
    Const SpellCurr As String = "dollar,only,and,cent"
    This line of code, at the top of the module, enables the change of currency. Just replace the words “dollar” and “cent” with the names pertaining to another currency.
  2. Supplying function arguments
    The following parameters are supplied to the function:
    • WithCurr
      By default the number will be written out without any currency.
      You can set this argument to True to include the specified currency.
    • NoDecs
      By default decimal places will be shown, added if missing in the number.
      Set this argument to True to suppress display of decimals.
    • SpellDecs
      By default (meaning, if you don’t supply this argument) decimal places will be written as a fraction, like 10/100. If you set this argument to True the output will be like Ten Hundredth.
    • CaseType
      By default each word will be written with an upper case first letter followed by lower case letters for the rest of the word. However, you can set this property to any of he following values:
      NctLower ' = all lower case
      NctFirst ' = Only first character in upper case
      NctProper ' = Each word's first character capitalised (Default)
      NctUpper ' = all caps

The procedure’s name is WriteAmountInWords, and the best way to call it may be by an event procedure installed in the code module of the sheet where the amount is entered. This is an example:-
Code: Select all
 Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const AmountCell As String = "B5"
    If Target.Address = Range(AmountCell).Address Then
        WriteAmountInWords Target.Value
    End If
End Sub
Change the String “B5” to reflect the cell reference where the amount will be found. Whenever that cell is changed WriteAmountInWords will be executed.

WriteAmountInWords is where you can set all the arguments described above. If you need different parameters for different uses you can program them in this procedure. Here, you must also specify the target cell for the output. Adjust the constant TargetCell to contain the reference to the cell you wish to write to. WriteAmountInWords calls the procedure SpellAmount that does the rest of the work.

Apart from the above event procedure all the code is contained in a single module called SpellNum which you can extract from the attached workbook. You can simply drag the module into your own project in VBE's Project Explorer or paste the code into any new or existing module you may have. The name of the module isn’t of significance.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 19 guests