O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.2. VBA Procedures

VBA code can be structured clearly and efficiently by breaking up sections of code into logical "chunks" called procedures. In this section, you'll see how to use the different types of VBA procedures and to employ good practices in their design.

8.2.1. Function or Sub?

A common area of confusion among novice VBA programmers is whether to write a function or a sub (short for "subroutine"). Some developers create functions for every procedure they write, in the belief that they are better in some way. They aren't. Functions and subs are just two kinds of procedures, and they both have their purposes. A quick way to determine which one is more appropriate is to ask this question: Does my procedure do something or return something?

If the purpose of your procedure is to compute or retrieve a value and return it to the calling procedure, then of course you should use a function. After all, functions are designed to return a single value to the calling procedure. They do it efficiently and easily, and they can be used directly in queries and calculated controls on forms and reports. They can even be used directly in macros.

Functions tend to have names that are nouns, like LastDayOfMonth or FullAddress. For example, a control on a report might have this Control Source property value:

=LastDayOfMonth(Date())

The field would display the results of calling some function called LastDayOfMonth with the parameter value of today's date.

On the other hand, if the main ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required