Streamlining Numbers to Words Converter ~ LEARN MS-ACCESS TIPS AND TRICKS

Sunday, December 3, 2023

Streamlining Numbers to Words Converter

Numbers to Words Converter.

While converting numbers to words is straightforward in Microsoft Word Mail Merge, such functionality is not readily available in MS Access. To address this limitation, we have developed a versatile function that can be employed wherever needed—whether within a Form TextBox Control, on Report Summary Totals, in Printed Invoices, or any other desired location. Utilize the CardText() function by calling it with the desired number for conversion and displaying the result wherever necessary. It's as simple as that.

The Main Demo Form Image is given below:

The Demo Form in Design View.

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

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 during the rounding process of the fractional part of the value. 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 into words and subsequently presents it in the Label Control situated below.

In addition to inputting a straightforward numeric value, you have the flexibility to compose an expression for calculation purposes. 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. It is a simple interface to enter the Function Parameter values and Call the Function.

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 features an expression: "=CardText([Calc])." This expression employs 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 for converting numeric inputs into their textual representations.

Preparing for the Streamlining VBA Code Procedure.

Only one TextBox has the AfterUpdate Event, when fired it simply calls the Command Button Click Event to take over the task of validating the value entered into the TextBox and to run the CardText() Function.

Given that the form boasts a straightforward interface with minimal events to manage, the streamlining procedure allows for handling 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 contributes to a more concise and manageable implementation.

We need only one Instance of the TextBox Control and Instances of two Command Buttons with different Names in the Intermediary Class Module. All three Object Instances are declared with the keyword WithEvents so that we can write their Event Procedures in the Card_ObjInit() Class.

In this particular scenario, opting for object-level Wrapper Classes might entail a more extensive VBA codebase to handle events. Following the prescribed guidelines would necessitate Wrapper Classes, particularly for the Command Buttons. Additionally, the use of a Collection object becomes imperative to keep the instances of wrapper classes alive in memory, ensuring the capture and execution of event procedures.

However, considering the simplicity of the form interface and the limited number of events to manage, this approach may introduce unnecessary resource overhead. If the streamlined solution in the intermediary class module proves sufficient for handling these events, it can lead to a more resource-efficient implementation. This decision should be based on a balance between adhering to coding standards and optimizing resource usage for the specific requirements of the form.

Therefore, in this case, we decided to go along with managing everything in the Card_ObjInit Class Module itself.

The Card_ObjInit Wrapper Class.

The Card_ObjInit Class, this is a Wrapper Class too, VBA Code 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.

At the global declaration area, the TextBox instance is defined with the object name 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 execute the validation check and to call the CardText() Public Function.