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.
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 InsertOProcedure 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()
In most cases, you want your function to accept one or more values that you pass to it as data for the function to operate on. For example, the Sqr() function accepts a single argument, which must be a number. To define the arguments that your custom function accepts, use the following syntax, inside the parentheses that follow the function name:
name As Type where name is just some name that you make up to use as a placeholder for the incoming value, and Type is a valid data type. For example, you might want the custom SalesTax() function to accept a single numeric value as an argument. You need to make up a name for that, so just call it AnyNum. You also have to define that incoming value as some sort of number. Most likely, the passed value is a Currency value anyway, so you can modify the custom SalesTax() function as follows to accept a single number as an argument:
Public Function SalesTax(AnyNum As Currency) End Function
What the first line really means is "Expect some number to be here when called. Refer to that number as AnyNum and treat it as a Currency number."
A function can accept any number of arguments. If you want a function to accept multiple arguments, give each argument a name and data type by using the same preceding syntax. Separate each definition with a comma. The SalesTax() function needs to accept only one argument, so don't modify that one. However, just as a general example, if you want a function to accept two arguments, you define each as in this example:
Public Function funcName(AnyNum As Currency, AnyText As String) End Function
A function can also return a value — that is, only one value because a function can't return multiple values. To make your function return a value, you just add
As Type where Type is a valid data type, to the end of the first statement, outside the closing parenthesis of the function name. You specify only the data type of the returned value — don't give it a name. For example, you might want the SalesTax() function to return a single value that's a Currency number. In that case, modify the SalesTax() function this way:
Public Function SalesTax(AnyNum As Currency) As Currency
The custom function doesn't return its value until all the code in the procedure has been executed. To define the value returned by the function, use the syntax functionName = value where functionName is the same as the name of the function itself, without the parentheses, and value is the value that you want the function to return (although the value can be an expression that calculates a return value).
Suppose you want to be able to pass to the SalesTax() function some Currency value, like $100.00 or $65.45 or whatever, and have it return the sales tax for that amount. To pick a number out of a hat, the sales tax rate is 6.75 percent. The following SalesTax() function performs the appropriate calculation (by multiplying the number that's passed to it by 0.0675) and then returns the results of that calculation:
Public Function SalesTax(AnyNum As Currency) As Currency 'Multiply passed value by 6.7 5% (0.0675) and 'return the result of that calculation. SalesTax = AnyNum * 0.0 67 5 End Function
You might remember, earlier in this chapter, when we said that a public custom function in a standard module can be used anywhere that a built-in function can be used. After you type in the SalesTax() function, you can see that for yourself by testing it the same way that you test a built-in function. For example, if you type the following line into the Immediate window
and then press Enter, you get
because the sales tax on $100.00 is $6.75. If you type
and press Enter, you get 1.0118 because the sales tax on $14.99 is about $1.02.
In case you're wondering why all the numbers aren't automatically rounded off, it's because the Immediate window always displays its results as sort of a plain number. In real life, you don't create a function just to use it in the Immediate window. More likely, you use the custom function in queries, forms, reports, or macros.
Suppose you create the preceding SalesTax() function and then choose FileOClose and Return to Microsoft Office Access from the VBA Editor menu bar. Next, you want to create a query that lists the unit price and sales tax for all the records in a table. Because you can use a custom function just like you use a built-in one, you can set up the query as shown in the Query Design portion of Figure 11-2, where the Unit Price column refers to a field in the Order Details table, and Tax is a calculated field that uses the custom SalesTax() function.
Custom Sales Tax() function used in a query's calculated field.
Custom Sales Tax() function used in a query's calculated field.
The lower half of Figure 11-2 shows the results of the query in Datasheet view. The Unit Price column displays the unit price from each record in the underlying table. The Tax column shows the sales tax amount for each unit price.
The query in Figure 11-2 is just an example, of course. You can use the custom SalesTax() function anywhere that you could use a built-in function, such as in the Control Source property of a calculated control or wherever you would use a built-in function in a VBA statement.
Was this article helpful?