MS-Access / Getting Started

Role of Functions in VBA

All the functions that are available to you in Access are also available to you in VBA. In VBA, you use the same function syntax that you use in Access. In Access, the Expression Builder is a good tool for finding out which functions are available as well as how to use them. To open the Expression Builder, open a form in Design view, click a text box control, click a property that can accept an expression (like Control Source or Default Value), and then click the Build button in that property.

After you're in the Expression Builder, click the plus sign (+) next to functions and then click Built-In Functions. If you then select <All> from the top of the middle column, the right column lists all the built-in functions in alphabetical order. Optionally, you can click a category name in the middle column to limit the third column's list to just the functions in that category.

When you click the name of a specific function in the third column, the syntax for using that function appears in the lower-left corner of the Expression Builder.

Before you go trying to create your own custom functions, we recommend knowing which functions are already available to you as built-in functions. You don't need to reinvent the wheel by creating a custom function that duplicates a built-in function.

Every function returns some value. For example, the Date() function returns the current date. You can see this for yourself right in the VBA Editor Immediate window. For example, if you type this line

? Date()

into the Immediate window and press Enter, the Immediate window shows the value returned by the Date function, which is the current date.

We suppose we should point out that sometimes in VBA, you can often omit any empty parentheses that follow a function name. In fact, the VBA Editor might even remove the parentheses for you, and the statement will still work after the VBA Editor removes the parentheses. For example, if you enter ? Date in the Immediate window, you get the same result if you enter ? Date( ). However, if the parentheses aren't empty, you should definitely include both the opening and closing parentheses in your code.

Look at another example. The Sqr() function accepts a single number as an argument and returns the square root of that number. For example, if you type the line

? Sqr(81)

into the VBA Editor Immediate window, you get back 9, which is the square root of 81.

It often helps to imagine that the word of follows a function's name. For example, think of ? Sqr(81) in the Immediate window as "What is the square root of 81?"

[Contents] [Next]