Page 86 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 61

Session 4 — Syntax and Data in VBA 61

Understanding Variable Scope

A variable that is declared in a VBA program is available only in certain parts of the pro-
gram. This is referred to as the variable’s scope, and a variable can be used only within its
scope. In other parts of the program, it effectively does not exist. In fact, you can have two
variables of the same name with different scopes, and they are completely independent of
each other.
VBA offers three levels of scope:
Procedure level. Scope limited to a single VBA procedure.
Module level. Scope limited to a single module (all procedures in the module).
Project level. Scope extends throughout the project.
To create a variable with procedure-level scope, declare it using a Dim statement inside a
procedure. You should always use procedure-level scope unless there is a specific reason to
use a wider scope. Most variables in your program will have this scope.
To create a variable with module-level scope, declare it using a Dim statement at the
module level (outside any procedures). Use module-level scope when multiple procedures
in the module need access to a variable, as is the case with property procedures (which are
covered in Session 26). In this context, you can use Private in place of Dim, but it has the
same effect:

Private Total As Integer
Project-level scope can take two forms. If you declare a variable at the module level using
the Public keyword, its scope extends throughout all modules in the current project as well
as any other open VBA projects. If you include the statement Option Private Module at
the start of the module, variables declared with Public have scope throughout the current
project only.
When two variables with the same name have overlapping scopes, the variable
with the more restrictive scope takes precedence.
Note
When deciding what scope to use for a variable, the rule is to use the most restrictive
scope possible. Most variables in a program have procedure-level scope. By isolating variables
within procedures, you remove the possibility of unintended changes and interactions and
the resulting program bugs and errors. Information that must be shared between two or more
procedures can be passed back and forth by means of procedure arguments and return values.
Variables with wider scope should be used sparingly, and only when it is unavoidable.