Page 120 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 95

Session 7 — Procedures and Modules 95

When you run TestStatic1, the output in the Immediate Window is as follows:

0 0
1 0
2 0
3 0
4 0

The Debug.Print statement and the Immediate Window are covered in
Session 2.
Cross-Ref
You can see that the static variable A maintained its value between calls to the procedure,
while variable B did not.

Procedure Scope

You learned about the concept of variable scope in Session 4. Like variables, procedures also
have scope, controlling the parts of the program from which the procedure can be called.
There are three levels of procedure scope:
Public. The procedure is visible to all modules in all projects. This is the default if
the procedure is defined with no keyword. You can also use the Public keyword,
which has the same effect.
Private. The procedure is visible only within its own module. To create a private
procedure, use the Private keyword:
Private Sub MyuSub()
Restricted public. If the Option Private statement is included at the start of the
module, procedures defined with the Public keyword or with no keyword are visible
to all modules in the current project, but not in other projects.

Storing Procedures

Procedures are stored in modules, but which modules? There is no single correct answer to
this question. Aside from possible limitations imposed by procedure scope, there are really
no limitations on where you place your procedures. You may want to give some thought to
organizing them in a convenient manner.
When you first start programming, it may be practical to combine all your VBA proce-
dures in a single module and then import that module into each project that you create.
Later, as your collection of useful procedures grows larger, you may want to organize them
into functional categories, each in its own module. Then, you can include a module in other
VBA projects as needed.