Beyond Excel - 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))

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)

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 Function

Additional Resources:
DspErrMsg()