Form VBA Structured Coding Numbers to Words Converter ~ LEARN MS-ACCESS TIPS AND TRICKS

Numbers to Words Converter.

While Microsoft Word Mail Merge provides a straightforward way to convert numbers into words, this functionality is not directly available in MS Access. To bridge this gap, we developed a versatile function that can be used wherever required—whether in a Form TextBox control, Report Summary, printed invoices, or any other context. Simply call the CardText() function with the desired number, and it will return the corresponding words for display. It’s that simple.

The Main Demo Form Image is given below:

Right-click to open Large Image in New Tab/Window.

The Demo Form in Design View.

Right-click to open Large Image in New Tab/Window.

Sample Report Image. The Group-level Subtotal Amount is printed in Words.

Right-click to open Large Image in New Tab/Window.

The CardText() Function VBA Code Listing.

Option Compare Database
Option Explicit

Public Function CardText(ByVal inNumber As Double, Optional ByVal precision As Integer = 2) As String
'------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2008/2023
'URL    : www.msaccesstips.com
'Version: 2.0
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim ctu, ctt, bmth
Dim strNum As String, j As Integer, k As Integer, fmt As String
Dim h As Integer, xten As Integer, yten As Integer
Dim cardseg(1 To 4) As String, txt As String, d As String, txt2 As String
Dim locn As Integer, xfract As String, xhundred As String
Dim xctu As String, xctt As String, xbmth As String

On Error GoTo CardText_Err

strNum = Trim(Str(inNumber))
locn = InStr(1, strNum, ".")
'Check Decimal Places and rounding
If locn > 0 Then
  xfract = Mid(strNum, locn + 1)
 strNum = Left(strNum, locn - 1)
    If precision > 0 Then
        If Len(xfract) < precision Then
            xfract = xfract & String(precision - Len(xfract), "0")
        ElseIf Len(xfract) > precision Then
            xfract = Format(Int(Val(Left(xfract, precision + 1)) / 10 + 0.5), String(precision, "0"))
        End If
        xfract = IIf(Val(xfract) > 0, xfract & "/" & 10 ^ precision, "")
    Else
        strNum = Val(strNum) + Int(Val("." & xfract) + 0.5)
        xfract = ""
    End If
End If

h = Len(strNum)
If h > 12 Then
'if more than 12 digits take only 12 (max. 999 Billion)
'extra value will get truncated from left.
   strNum = Right(strNum, 12)
Else
   strNum = String(12 - h, "0") & strNum
End If

GoSub initSection

txt2 = ""
For j = 1 To 4
    If Val(cardseg(j)) = 0 Then
       GoTo NextStep
    End If
    txt = ""
    For k = 3 To 1 Step -1
      Select Case k
       Case 3
            xten = Val(Mid(cardseg(j), k - 1, 1))
            If xten = 1 Then
                txt = ctu(10 + Val(Mid(cardseg(j), k, 1)))
            Else
                txt = ctt(xten) & ctu(Val(Mid(cardseg(j), k, 1)))
            End If
        Case 1
            yten = Val(Mid(cardseg(j), k, 1))
            xhundred = ctu(yten) & IIf(yten > 0, bmth(1), "") & txt
            Select Case j
                Case 2
                      d = bmth(2)
                Case 3
                    d = bmth(3)
                Case 4
                    d = bmth(4)
            End Select
            txt2 = xhundred & d & txt2
    End Select
   Next
NextStep:
Next

If Len(txt2) = 0 And Len(xfract) > 0 Then
    txt2 = xfract & " only. "
ElseIf Len(txt2) = 0 And Len(xfract) = 0 Then
    txt2 = ""
Else
  txt2 = txt2 & IIf(Len(xfract) > 0, " and " & xfract, "") & " only."
End If

CardText = txt2

CardText_Exit:
Exit Function

initSection:
'Units to 19
xctu = ", One, Two, Three, Four, Five, Six, Seven, Eight, Nine, Ten, Eleven, Twelve,"
xctu = xctu & " Thirteen, Fourteen, Fifteen, Sixteen, Seventeen, Eighteen, Nineteen"
ctu = Split(xctu, ",")

'Tens
xctt = ", Ten, Twenty, Thirty, Fourty, Fifty, Sixty, Seventy, Eighty, Ninety"
ctt = Split(xctt, ",")

xbmth = ", Hundred, Thousand, Million, Billion"
bmth = Split(xbmth, ",")
k = 4
For j = 1 To 10 Step 3
    cardseg(k) = Mid(strNum, j, 3)
    k = k - 1
Next
Return

CardText_Err:
CardText = ""
MsgBox Err.Description, , "CardText()"
Resume CardText_Exit
End Function

The CardText() function, first written and published in January 2009, accepts two parameters. The initial parameter should be either a Decimal Number or a valid expression that resolves to a Decimal Number. The second parameter determines the precision of decimal digits. Notably, the second parameter is optional and already seeded with a default value of 2. The precision setting can be modified when invoking the CardText() function to align with specific requirements.

How to Run the Function on the Form.

Upon entering a numeric value in the first TextBox, you can execute the CardText() function by pressing the Enter key or by clicking on the Show Command Button. This operation converts the entered number to words and subsequently presents it in the Label Control situated below.

In addition to entering a straightforward numeric value, you can compose an expression for calculation and get the result. The CardText() function processes this expression, and the resulting value is transformed into words for display. A sample expression is demonstrated below:

((625*25+0.75)*0.80)

There are two Command Buttons. One to run the Function and the other to close the Form. The Label Control displays the entered Number in Words. A simple interface allows you to enter the required parameters and call the function seamlessly.

To illustrate the straightforward application of the CardText() function, two TextBox Controls have been incorporated beneath the Close Command Button. Specifically named "Calc," the first TextBox is unbound. Users can input a numeric value into this TextBox. The adjacent TextBox, on the right side, contains the expression "=CardText([Calc])." This expression uses the CardText() function to convert the value entered into the "Calc" TextBox, presenting it in words within the same TextBox on the right side. This intuitive setup demonstrates the seamless integration of the CardText() function, which converts the number into its textual representation.

Preparing for the Streamlining VBA Code Procedure.

Only one TextBox has the AfterUpdate Event. When fired, it simply calls the Command Button Click Event, validates the input value in the TextBox, and runs the CardText() Function.

Given that the form boasts a straightforward interface with minimal events to manage, the streamlining allows these uncomplicated event procedures within the intermediary class module. Consequently, there is no imperative need for wrapper classes. This simplifies the structure and enhances efficiency by consolidating the handling of basic events directly within the intermediary class module, eliminating the necessity for additional layers of abstraction. This approach streamlines the code and enables a more concise and manageable implementation.

We require only a single instance of the TextBox control and two instances of the CommandButton control, each with a different name, in the intermediary class module. All three object instances are declared using the `WithEvents` keyword, enabling their event procedures to be implemented within the `Card_ObjInit` class.

In this particular scenario, adopting object-level wrapper classes would likely result in a larger VBA codebase for event handling. Following the recommended design guidelines would require wrapper classes, especially for the CommandButton controls. In addition, a `Collection` object would be necessary to maintain the wrapper class instances in memory, ensuring that their event procedures remain active and can be executed when the corresponding events occur.

However, given the simplicity of the form interface and the limited number of events, the coding approach would introduce unnecessary complexity and resource overhead. If the streamlined implementation in the intermediary class module is sufficient to manage these events, it provides a more resource-efficient solution. The choice should therefore be based on balancing adherence to established coding practices with the optimized resource usage for the specific Form requirements.

Accordingly, for this implementation, we chose to manage all event handling directly within the `Card_ObjInit` class module.

The Card_ObjInit Wrapper Class.

The Card_ObjInit Class, which is a Wrapper Class, is listed below.

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmdS As Access.CommandButton
Private WithEvents cmdE As Access.CommandButton
Private frm As Access.Form

Public Property Get m_Frm() As Access.Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef vfrm As Access.Form)
    Set frm = vfrm
    
    Call Class_Init

End Property

Private Sub Class_Init()
Const EP = "[Event Procedure]"

Set cmdS = frm.cmdResult
    cmdS.OnClick = EP
Set cmdE = frm.cmdClose
    cmdE.OnClick = EP
Set txt = frm.Amt
    txt.AfterUpdate = EP
End Sub

Private Sub cmdE_Click()
If MsgBox("Close the Form? ", vbYesNo + vbQuestion, "CmdClose_Click()") = vbYes Then
    DoCmd.Close acForm, frm.Name
End If
End Sub

Private Sub txt_AfterUpdate()
 Call cmdS_Click
End Sub

Private Sub cmdS_Click()
Dim tx As Variant
Dim t As Variant
Dim ctxt As String
Dim Rounding As Integer
Dim dblResult As Double
Dim msg As String
Dim fmt As String

On Error GoTo cmdResult_Click_Err
tx = frm!Amt
t = Replace(tx, ",", "")
tx = t
Rounding = frm!RoundTo
fmt = "#,##0." & String(Rounding, "0")
dblResult = Eval(tx)

If dblResult > (10 ^ 12 - 1) Then
  msg = "Value: " & dblResult & " Exceeds permissible limit."
  MsgBox msg, , "cmdResult_Click()"
Else

frm!Amt = Format(dblResult, fmt)

    ctxt = CardText(dblResult, Rounding)
    frm!Result.Caption = ctxt
End If

cmdResult_Click_Exit:
Exit Sub

cmdResult_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdResult_Click()"
Resume cmdResult_Click_Exit
End Sub

The Intermediary Class Module.

In the global declaration area, the TextBox instance named txt and two Command Button Control instances, cmdS and cmdE, are also declared. Each of these instances is qualified with the 'WithEvents' keyword, empowering them to capture events triggered on the form.

At the onset of the Class_Init() Subroutine, the cmdS Command Button object, labeled Show, is linked to the cmdResult Command Button through the assignment. Simultaneously, the cmdE Command Button object is associated with the cmdClose Command Button. Both of these Command Button objects have their OnClick() events enabled. Additionally, the txt object is connected to the Amt TextBox, and its AfterUpdate event is activated.

The cmdE Click Event Subroutine closes the Main Form.

In the cmdS Click Event Procedure, the entered value is validated, and the CardText() Function is invoked to convert the number to words. The resulting output is then displayed in the Label Control on the Form. If the input is an expression rather than a direct number, it is first evaluated to a number before calling the CardText() Function.

The AfterUpdate () Event of the Amt TextBox calls the cmdS_Click() Event Subroutine to run the validation check, and subsequently calls the CardText() Public Function.