Creating Custom Microsoft Access Formulas and Using Them Effectively

Creating Custom Access Formulas & Using (1)
MS Access has a very powerful support for using expressions in your forms/reports’ controls. For example, showing a sum of some column can be as simple as putting down:

=Sum([SomeColumn])

and we’re done!

Now, sometimes, we don’t have simple requirements. We might need to calculate a percentage, which would be expressed as:

=Sum ([AmountPaid]) / Sum([InvoiceAmount])

This will work but not exactly without hitches. For example, what happens if the InvoiceAmount adds up to $0? We would, of course, get a division by zero error which is then shown as #Div/0!. Not very pretty.

Several people work around this problem by doing this instead:

=IIf(Sum([InvoiceAmount]) = 0, Null, Sum ([AmountPaid]) / Sum([InvoiceAmount])

This gets us a clean result once again. But it’s no longer straightforward to read. I don’t know about you but whenever I inherit a database that is heavy with expressions like those, it can be quite a chore to go through the expressions and think about the path it takes to arrive at the result, which slows down my troubleshooting or understanding of what this is going on within the form. Unfortunately, sometimes the expressions are much messier than the tidy examples I showed so far.

As of late, I’ve come to appreciate using custom functions rather than expressions. Creating custom access formulas and functions in VBA offer several advantages over using expressions. For one, you can easily debug VBA code to aid validating the logic or the result. Next, you don’t have to worry about balancing parentheses and square brackets as VBA allows you to write the same expression above but simply, like this:

If curTotalAmount Then
varPercentage = curAmountPaid / curTotalAmount
Else
varPercentage = Null
End If

There is no need to repeat the expression (e.g. the “Sum([InvoiceAmount])”; this can be done once then analyzed in the If/Then block. Not to mention, the fact that you can leave comments in your VBA functions allows you to revisit it months later and see why it’s doing the calculations that way.

Seems more enticing doesn’t it? Let’s think about design patterns we can adopt for those custom functions.

Where Can I Put It?

If you think the answer is standard VBA module, yes, that is one possible place, but is it ideal? Personally, I prefer to follow the same principle we apply toward our variables — declare it with the smallest scope needed for its job. What people might not realize is that you can define custom functions directly on the form’s or report’s module, and you can refer to those VBA functions from any controls of the same form/report. This essentially makes the custom function “private” to the form/report and can generally be a good thing for keeping your forms and your VBA code cohesive and loosely coupled.

You certainly don’t want to end up with a general VBA module that houses several VBA functions that will be used throughout the projects. This is especially true when the functions themselves appear as expressions within controls. Those sorts of references can’t be back-tracked and found in VBA code. Essentially, a VBA code function that is used as an expression in a standard module won’t tell you where it is being used. Talk about picking a needle in haystack. Thus, the only time such functions should appear in a standard VBA module is when you actually intend it to be used across several forms. However, if I’m starting with a new custom VBA function, I always house it in form/report’s module first.

Which Data Types To Use?

If you’ve programmed with VBA long enough, you might want to use specific data types. It might be very reasonable to define your function as this:

Public Function CalculatePercentage( _
TotalAmount As Currency, _
AmountPaid As Currency _
) As Double

But in practice, this actually causes problems. How do you handle nulls? How do you handle bad values that are being sent to those functions? You may end up with errors such as #Type! or something else in those cases. You also cannot debug in those cases because the problems start with attempting to call the function itself, thus errors are at the expression service layer, rather than VBA layer.

So, for those functions where the intention is to be used as an expression, it is highly desirable to define the function like this:

Public Function CalculatePercentage( _
TotalAmount As Variant, _
AmountPaid As Variant _
) As Variant
Dim curTotalAmount As Currency
Dim curAmountPaid As Currency
Dim dblPercentage As Double

‘Attempt to coerce variants into currency
On Error Resume Next
curTotalAmount = CCur(TotalAmount)

If Err.Number = 0 Then
curAmountPaid = CCur(AmountPaid)
End If

‘If either fails, Err.Number will be nonzero and we will return null
If Err.Number Then
CalculatePercentage = Null
Exit Sub
End If

On Error GoTo ErrHandler

‘code for performing calculation

ErrHandler:
CalculatePercentage = “#Error – ” & Err.Description
End Function

As you see, instead of typing our inputs, we adopt an approach where we accept any inputs then attempt to coerce it to currency. This ensures that the function will work with a large variety of inputs and not throw up errors, either within the VBA or within the expression service call.

We then test the result of the coercion, if either fails, it’ll set Err.Number and we can just silently return Null. You might prefer to put out a diagnostic message and you certainly can do that instead of returning Null. You have the flexibility. This also illustrates why you want the return type to also be a variant. Though the function “should” return a double, it may either return null on null inputs or maybe a string with error if there was a problem in the code for doing calculation, as can be seen in ErrHandler section.

A Note on Error Handling

Note that the ErrHandler also does not use MsgBox(). When a function is referenced in an expression, it can be annoying indeed to get repeated MsgBox() every time the function is called, and that function can be potentially called several times. Thus, it’s much better to simply return the error information as a string which will then be printed into the control for you to then diagnose and correct if appropriate.

Summary

Using VBA functions as expression can be very advantageous over nesting multiple functions and column references. In addition, they will offer you more flexibility and control in how to express your logic. To make best use of them, follow these 3 guidelines:

1) If it’s used in only one form/report, scope it to the form’s or report’s VBA module, rather than housing it in standard VBA module.

2) Use variants for both inputs and return type. Validate & cast the inputs within the function rather than strong-typing the functions’ parameters to avoid problems with calling the functions due to data type incompatibility.

3) If there are common conditions, such as division by zero, that makes function inapplicable, you can choose to return Null or maybe a string such as “N/A” in lieu of the actual output.

4) Return any errors as a string, rather than displaying a MsgBox() which may be repeatedly displayed far too many times.

I hope this helps you. What is your oft-used expressions?