Page 89 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

In the preceding procedures, both Procedure1 and Procedure2 have declared a variable
named intCounter. In Procedure1, intCounter has been set to 87, this means intResult will be
set to 6438 when the third line is executed. In Procedure2, intCounter has not been set to a
specific value so it retains the initialized value for an Integer, which is 0. Then the second line
is executed, and intResult is given a value of 0 also (0 * 74 = 0).
To use a variable in more than one procedure, it needs to be declared at the module level.
Variables declared at the module level can be used by any procedure that is within the same
module. To declare a module variable, the declaration statement for the variable needs to be
entered in the Declaration section of the module (following any Option statements and before
any procedures), as shown in Figure 4-12.

Chapter 4

Figure 4-12. If you want a variable to be available to all procedures in a module, you need to
declare the variable in the declaration section for modules.

Module variables can be exposed to other modules by using the Public modifier. When a vari­
able is declared Public, it becomes visible to all other modules, not just the module it is
declared in.
Although all module variables are private by default, for clarity it is better to declare them
using Private instead of Dim. Variables that are to be visible to all modules are declared using
the Public statement. The following two declarations illustrate the difference between a Public
and a Private declaration:
Private intThisModuleOnly as Integer
Public intAllModules as Integer

63
Part 2: Visual Basic for Applications