Creating Your Own Functions

In VBA, you can create your own, custom functions to add to those that are built into Access. As a rule, put all custom functions in a standard module rather than in a class module because putting a custom function in a standard module makes the function available to all the objects in the current database. In other words, any function that you create in a standard module can be used just as though it were a built-in function throughout the current database.

Work through the whole process, starting with a simple example of a custom function that calculates and returns the sales tax for any numeric value that's passed to it. You can put the function in any standard module - it doesn't really matter which. For this case, just start with a new, empty standard module.

1. In the Access database, click the Create tab.
2. In the Other group, select Module from the drop-down list on the far right side of the Ribbon.
   You're taken to the VBA Editor with a brand-new, almost empty module to work with.
   Tip: All modules have the words Option Compare Database at the top already, so that's why we say that the module is almost empty. That first declaration, Option Compare Database, just tells the module that any comparisons using operators like = or > should be performed using the same rules as the rest of the current database. There's no need to change that line.
3. Choose Insert → Procedure from the VBA Editor menu bar.
   The Add Procedure dialog box opens, asking for the name, type, and scope of the procedure.
   The name must start with a letter and cannot contain any blank spaces. For this example, you can name the function SalesTax.
4. Choose Function as the type (because you're creating a custom function) and Public as the scope (so that all other objects within the database can use the function).
5. Click OK in the Add Procedure dialog box.
   The module contains the first and last lines of the procedure:

   Public Function SalesTax()
   End Function