Function or

A common area of confusion among novice VBA programmers is whether to write a Function or a Sub (short for "subroutine"). Many 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 compute something?

If the purpose of your procedure is to compute or retrieve a value and return it to the calling procedure, then by all means 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, but you don't use macros, do you? (After all, you're reading this book.)

Functions will tend to have names that are nouns, like LastDayOfMonth or FullAddress. For example, a control on a report might have a control source property of


This 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 purpose of your procedure is to do some action and there is no clear-cut value to return, use a Sub. Many programmers think that they must return something, even if they have to make some artificial return code or status. This practice can make your code harder for others to understand.

Subs will tend to have names that are verbs, like LoadWorkTable or CloseMonth. In practice, your code will look like this:


Pretty easy, right? Any developer looking at this line of code can see the obvious: a Sub called LoadWorkTable is being called, and it doesn't return a value.

0 0

Post a comment