Part II: Programming Microsoft Access
436
you don’t run the risk of an error occurring from having a procedure and module with the
same name.l (^) A procedure can’t contain other procedures within it. A procedure can, however, call
another procedure and execute the code in the other procedure at any time.
Because of the rules governing procedure scope, you can’t have two public procedures both named
MyProcedure, although you could have two private procedures, both named MyProcedure, or
one public procedure named MyProcedure and one private procedure named MyProcedure.
The reason it’s a bad idea to use the same procedure name for multiple procedures, even when the
procedures have different scopes, should be obvious.
The following sections cover some of the specifics regarding VBA procedures. Planning and compos-
ing the procedures in your modules is the most time-consuming part of working with VBA, so it’s
important to understand how procedures fit into the overall scheme of application development.
Subroutines and functions both contain lines of code that you can run. When you run a subroutine
or function, you call it. Calling, running, and invoking are all terms meaning to execute (or run) the
statements (or lines of code) within the procedure or function. All these terms can be used inter-
changeably (and they will be, by different developers). No matter how you invoke a VBA proce-
dure — using the Call keyword, referencing the procedure by its name, or running it from the
Immediate window — they all do the same thing, which is to cause lines of code to be processed,
run, executed, or whatever you want to call it.
The only real difference between a procedure and a function is that, when it’s called, a function
returns a value — in other words, it generates a value when it runs, and makes the value available
to the code that called it. You can use a Boolean function to return a True or False value indicat-
ing, for example, where the operation the procedure performed was successful. You could see if a
file exists, if a value was greater than another value, or anything you choose. Functions return
dates, numbers, or strings; functions can even return complex data types such as recordsets.
A subprocedure does not return a value. However, although a function directly returns a value to a
variable created as part of the function call, there are other ways for functions and subprocedures
to exchange data with form controls or declared variables in memory.
Understanding where to create a procedure
You create procedures in one of two places:
l In a standard VBA module: You create a subprocedure or function in a standard module
when the procedure will be shared by code in more than one form or report or by an
object other than a form or report. For example, queries can use functions to handle very
complex criteria.
l Behind a form or report: If the code you’re creating will be called only by a single proce-
dure or form, the subprocedure or function should be created in the form or report’s
module.