Amt2Wrd()
Amt2Wrd stands for Amount To Words. This type of function is also referred to as Number to Words but its use is almost always for converting currencies.
This UDF (User Defined Function) converts a dollar amount into words which is required for printing checks. Here is an example:
12,345.67 -> Twelve Thousand Three Hundred Forty-Five Dollars and 67 Cents
Learning Opportunities
There are a couple of learning opportunities for beginning coders here:
This makes extensive use of arrays so if you are looking for some array examples, this is a good routine to examine.
This is designed to work with dynamic arrays as well as single values or called from a VBA routine. This is accomplished by adding four variables (see Dynamic Array Portion in the variable declarations code block), testing the input parameter (see Dynamic Array Processing code block), and if it is an array, looping through all rows and columns. To learn more about this technique click Make UDFs Dynamic Array-able
This uses recursion (a routine that calls itself) to simplify the code. This technique makes it easy to retrofit other function so they too can be dynamic array aware.
We should always test our code. Here is how I tested Amt2Wrd(). I first created a dynamic array by placing this in cell A1 of an empty worksheet.
=LEFT(TEXTJOIN("",,MOD(SEQUENCE(1,15),10)),SEQUENCE(15))/100
Formula Breakdown:
That's a complicated formula (sorry) so let me break it down so beginners can understand it better.
SEQUENCE(1,15) I use this new dynamic array function to generate 15 numbers (1-15).
Why 15? Because after 1 trillion Excel starts truncating and inflation isn't that bad yet..MOD(SEQUENCE(1,15),10) converts those 15 numbers into a sequence of 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5.
TEXTJOIN("",,MOD(SEQUENCE(1,15),10)) combines the individual digits into a single string: "123456789012345"
LEFT(TEXTJOIN("",,MOD(SEQUENCE(1,15),10)),SEQUENCE(15)) This cuts the string into an array of 1 character, 2 characters, 3 characters etc.
LEFT(TEXTJOIN("",,MOD(SEQUENCE(1,15),10)),SEQUENCE(15))/100 Dividing the character strings by 100 lets Excel know we want those character strings converted to values and provides us the cents portion of our test numbers.
In cell B1 I added our UDF:
=Amt2Wrd(A1#)
The A1# tells Excel I want the spilled array, not just cell A1.
Here is the result.
Code
Below is the code. I believe it is easily modifiable for other currencies and languages.
Function Amt2Wrd(ByVal vAmount As Variant) As Variant
' Description:Convert dollar amount to words
' Inputs: vAmount Dollar Amount
' Outputs: Me Success: Dollar amount string
' Failure: vbNullsting or xlErrRef
' Requisites: *None
' Example: ?Amt2Wrd(123.45)
' Date Ini Modification
' 02/04/22 CWH Initial Development
' 03/31/22 MDN Corrections (Michael D Newby)
' Declarations
Const cRoutine As String = "Amt2Wrd"
' Dynamic Array Portion
Dim lRow As Long 'Array Row #
Dim lCol As Long 'Array Column #
Dim vValues As Variant 'Input Array
Dim vResult As Variant 'Result Array
' Individual Element Portion
Static sDenom As Variant 'Comma separator denominator in words
Static lDenom As Variant 'Comma separator denominator value
Static sOnes As Variant 'Ones in words array
Static sTeens As Variant 'Teens in words array
Static sTens As Variant 'Tens in words array
Dim lCount As Long 'Loop Counter
Dim sCents As String 'Cents portion of Result
Dim lEval As Long 'Evaluation result
Dim lTemp As Long 'Temporary result
' Error Handling Initialization
On Error GoTo ErrHandler
Amt2Wrd = vbNullString
' Initialize Variables
If Not IsArray(sDenom) Then
sDenom = Array("", "Thousand ", "Million ", "Billion ", "Trillion ")
lDenom = Array(1, 1000, 1000000, 1000000000, 1000000000000#)
sOnes = Array("One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
sTeens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", _
"Sixteen", "Seventeen", "Eighteen", "Nineteen")
sTens = Array("Ten", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End If
' Procedure
' Dynamic Array Processing
If IsArray(vAmount) Then
vValues = vAmount
ReDim vResult(LBound(vValues, 1) To UBound(vValues, 1), _
LBound(vValues, 2) To UBound(vValues, 2))
For lRow = LBound(vValues, 1) To UBound(vValues, 1)
For lCol = LBound(vValues, 2) To UBound(vValues, 2)
vResult(lRow, lCol) = Amt2Wrd(vValues(lRow, lCol))
Next
Next
Amt2Wrd = vResult
Else
' Individual Element Processing
' Convert cents and set vAmount dollar amount.
sCents = Right(Format(vAmount, ".00"), 2)
If sCents = "00" Then sCents = "No"
' Loop through comma seperator denominators
For lCount = 4 To 0 Step -1
lEval = Int(vAmount / lDenom(lCount))
If lEval > 0 Then
lTemp = lEval
' Hundreds
If lTemp > 99 Then
lTemp = Int(lTemp / 100)
Amt2Wrd = Amt2Wrd & sOnes(lTemp - 1) & " Hundred "
lTemp = lEval Mod 100
End If
' Tens
If lTemp > 19 Then
lTemp = lTemp - 10
Amt2Wrd = Amt2Wrd & sTens(lTemp - 1)
lTemp = lEval Mod 10
Amt2Wrd = Amt2Wrd & IIf(lTemp = 0, " ", "-")
End If
' Teens
If lTemp > 9 Then
lTemp = Int(lTemp / 10)
Amt2Wrd = Amt2Wrd & sTeens(lTemp) & " "
lTemp = 0
End If
' Ones
If lTemp > 0 Then Amt2Wrd = Amt2Wrd & sOnes(lTemp - 1) & " "
' Finish comma seperator denominator
Amt2Wrd = Amt2Wrd & sDenom(lCount)
End If
vAmount = vAmount - lEval * lDenom(lCount)
Next
' Finish up
If Amt2Wrd = vbNullString Then Amt2Wrd = "No "
Amt2Wrd = Amt2Wrd & "Dollar" & IIF(Amt2Wrd<>"One ", "s", "") & _
and " & sCents & " Cent" & IIF(sCents <> "01", "s", "")
End If
ErrHandler:
Select Case Err.Number
Case Is = NoError: 'Do nothing
Case Else:
' Called as UDF
If TypeName(Application.Caller) = "Range" Then
Amt2Wrd = CVErr(xlErrRef)
Else
' Called from VBA
Select Case DspErrMsg(cModule & "." & cRoutine)
Case Is = vbAbort: Stop: Resume 'Debug mode - Trace
Case Is = vbRetry: Resume 'Try again
Case Is = vbIgnore: 'End routine
End Select
End Select
End Function
Additional Resources:
DspErrMsg()