Custom Calculator and Eval Function

Introduction.

When we think of a calculator, the image that usually comes to mind is the traditional type with buttons labeled 0–9 and the standard arithmetic operators. Windows already includes such a calculator under Accessories, which can even be switched to Scientific mode — so there’s no need to recreate that in MS Access.

Instead, we’re going to design a different kind of calculator — one that can evaluate defined expressions consisting of data field names, constants, arithmetic operators, and parentheses (to control the order of operations). This calculator will allow you to input an expression, have Access evaluate it, and display the result instantly.

There’s no need for a complex interface. All we require is:

• A TextBox to enter the expression,

• A Command Button to trigger the evaluation, and

• A few lines of VBA code to process the calculation.

The result can be displayed either in a MsgBox or in another TextBox on the form.

Before we begin building the calculator, let’s look at one of Access’s most powerful yet underused built-in functions — the EVAL() function. This function will serve as the core engine of our custom calculator. With EVAL(), you don’t need to worry about following mathematical rules manually — such as evaluating parentheses first, then exponentiation, followed by multiplication and division (from left to right), and finally addition and subtraction. Simply pass the expression as a string to the EVAL() function, and it will return the correctly computed result.

EVAL() Function Usage

Try a few examples quickly to get a feel of it? Open the VBA Module Window (Alt+F11) and open the Debug Window (Ctrl+G). Type the following expressions in the Debug Window and press the Enter Key:

? EVAL("2+3*4/2")

Result: 8

? EVAL("(2+3)*4/2")

Result: 10

? EVAL("log(SQR(625))/log(10)")

Result: 1.39794000867204

? Eval("10^" & "Eval('log(Sqr(625))/log(10)')")

Result: 25

? EVAL("Datediff('yyyy',Forms!Employees!BirthDate,date())")

Result: 45 (the Employees Form must be open)

EVAL() the Expression Parser

From the above examples, we can see that you can write expressions in a TextBox using built-in functions, data field references, and numeric constants. The Eval() function then parses the expression and returns the result. This gives the user the flexibility to define and evaluate custom expressions dynamically, incorporating live data from form fields into their calculations.

Tip: The Eval() function can do much more than just evaluate formulas. It can also run other functions, trigger the Click event of a command button, or execute a macro programmatically. For additional details and examples of how Eval() can be used, open the Microsoft Access Help window in the VBA editor and search for “Eval Function.”

The sample Demo Project.

I have created a sample database (available for download at the bottom of this post) designed for an auto dealership that offers credit facilities to customers at nominal interest rates of 6%, 7%, or 8%, repayable in 24, 36, or 60 installments, respectively. The salesperson is responsible for determining the most suitable repayment plan for each customer, including the percentage of down payment, installment schedule, and applicable interest rate.

To encourage sales, the salesperson is also authorized to offer customers a discount of up to 20% on the Maximum Retail Price (MRP), based on negotiation and customer eligibility.

An image of the Form he uses to run these calculations is given below:

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

This is a stand-alone form (which can optionally be linked to a table) containing unbound text boxes. Each text box is labeled with its corresponding name displayed to the left. When writing expressions, it is essential to properly qualify the control names, for example:
Forms!CustomCalculator!Balance

Note that shorthand references such as Me!Balance are not accepted in this context. This can make it somewhat cumbersome to build expressions, especially when multiple field names are involved in a formula.

The VBA Code

We have created a small VBA routine that recognizes text box names enclosed in square brackets [ ], retrieves their corresponding values, and substitutes them into the expression before passing it to the Eval() function.

For easier expression entry, a combo box containing arithmetic operators and text box names (in square brackets) is provided on the form. The colored display control below shows, for informational purposes, the expression after the text box references have been replaced with their actual values. just before it is submitted to the Eval() function.

When the Calculate Command Button is clicked, the result of the calculation is displayed in the Text Box with a dark background and the label Result. The VBA Code is given below:

Private Sub cmdCalc_Click()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date    : November, 2008
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim str As String, loc1 As Integer, chk As Integer
Dim strout As String, loc2 As Integer, loc3 As Integer
Dim strin As String
Const sqrleft As String = "["
Const sqrright As String = "] "

On Error GoTo cmdCalc_Click_Err

'change the reference if different
str = Me![Expression]

loc1 = InStr(1, str, sqrleft)
If loc1 > 0 Then
   strin = Left(str, loc1 - 1)
   strout = Left(str, loc1 - 1)
   loc2 = InStr(loc1, str, sqrright)
End If
Do While loc2 > 0
   strin = strin & Mid(str, loc1, (loc2 - loc1) + 1)
   strout = strout & Me(Mid(str, loc1, (loc2 - loc1) + 1))
   loc1 = InStr(loc2 + 1, str, sqrleft)
   If loc1 > 0 Then
     loc2 = InStr(loc1, str, sqrright)
      If loc2 = 0 Then
         MsgBox "Errors in Expression, correct and retry. "
         Exit Sub
      Else
         strout = strout & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
         strin = strin & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
      End If
   Else
     loc3 = loc2
     loc2 = 0
   End If
Loop

If Len(str) > loc3 Then
   strout = strout & Mid(str, loc3 + 1)
End If

'this line can be removed if not required
Me![parsed] = strout

'change the reference, if different
Me![result] = Eval(strout)

cmdCalc_Click_Exit:
Exit Sub

cmdCalc_Click_Err:
MsgBox Err.Description, , "cmdCalc_Click()"
Resume cmdCalc_Click_Exit
End Sub

Private Sub cmdReset_Click()
Me![Expression] = Null
Me![parsed] = Null
End Sub

Note: There is no validation check included in the Code to detect misspelled names or unbalanced parentheses, etc. These shortcomings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections, and re-try.

Download

You can implement this program on any Form with a small change in str = Me![Expression] and Me![result] = Eval(strout) lines in the Code, if different names are used. Customize the Combo Box contents based on your input Field Names.

CustomCalc.zip

• Lost Links of External Tables
• Link External Tables with VBA
• Source Connect Str Property and ODBC
• Access Live Data in Excel-2
• MS-Access Live Data in Excel