Flexibility comes in two packages, as it were:-
- 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. - 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
- WithCurr
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
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.