Access™ 2007 VBA Programmer's Reference

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 ...