Testing a custom function

You might remember, earlier in this tutorial, 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 File → Close 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 in the Query Design portion, 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.

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.