The 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 what functions are available as well as how to use them. If you're in an Access Design view and don't see a Build button to click, you can click the Build button in the toolbar to open the Expression Builder.

After you're in the Expression Builder, click the + 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, as in Figure 11-1. 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.

Figure 11-1:

The Build button and the Expression Builder.

Figure 11-1:

The Build button and the Expression Builder.

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. For example, the Abs function is selected in Figure 11-1, so the window shows Abs(number). That just tells you that the Abs function expects a single number to be passed to it. For more information in the currently selected function, click the Help button in the Expression Builder.

Before you go trying to create your own custom functions, I recommend knowing what functions are already available to you as built-in functions. There's no need for you 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 could see this for yourself right in the VBA editor Immediate window. For example, if you type the following into the Immediate window and press Enter

the Immediate window shows the value returned by the Date function, which would be the current date.

I suppose I 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 closing 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 are not 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 following 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 meaning, What is the square root of 81?

0 0

Post a comment