Modutete Vet Versus procedureteVet

Unlike data stored in Access tables, data stored in VBA variables (including arrays and constants) doesn't last long. Each variable has a lifetime that defines how long it exists. Closely aligned with a variable's lifetime is its scope, which defines which objects in the database can and cannot access the variable. The scope and lifetime of a variable depend on where you define the variable within a module.

The top of a module, where you typically see Option Compare Database, is the declarations area. Here you can declare (announce) settings, variables, constants, and arrays to all procedures in the module.

For example, the line Option Compare Database is a module-level declaration that announces to all procedures in the module that this code is running within the context of a database. When you're comparing values in code using logic like equals or greater than, the code should use the same rules as the rest of the database.

You can also declare and assign values to variables, arrays, and constants in the declarations area of the module. Those variables have module-level scope and lifetime. These variables can be private — available only to the procedures in the module — or public — available to all other procedures in the Access database.

Variables, constants, and arrays declared inside a procedure have procedure-level scope and lifetime. Each variable defined within a procedure is visible to only that procedure and exists only while that procedure is running.

The significance of module-level versus procedure-level becomes more apparent as you gain experience coding in VBA. For now, the main thing to keep in mind is that module-level variables, constants, and arrays are declared at the top of a module, before the first procedure. Something that is procedure-level refers to variables, constants, and arrays defined within a procedure. Figure 4-4 illustrates the difference.

Figure 4-4:

Modulelevel and procedure-level declarations.

3 Microsoft Visual Basic VBA Practice - [Modulel (Code)]

! Eile' ,£dt. ¿i'-.y ^filiert tjefertj. RWti '.Vi:tni> If.


3 Microsoft Visual Basic VBA Practice - [Modulel (Code)]

! Eile' ,£dt. ¿i'-.y ^filiert tjefertj. RWti '.Vi:tni> If.

Option Compare Database 'Module-level variables,

' Public variables and cons Public Const pi As Double Public colors(5) As String

1 Private variables and cor Private length As Single! Private width As Single

onstants, and at = 3 .141592 653 61

ible to all procedures in all modules able to all procedures in THIS module only

SuJ 1 V


anySub() ariables, constants, anc |~Const c As Double = 34 ■A Dim energy As Double |_Dim mass As Double

1 Any code can follow. Sub

arrays below ai 59 6000000#

e only available in this procedure

Fu En

net ion anyFunc (frntfJaitie

_Tbim answer As Boolean ~|_Dim scales (3) As Intec

1 Any code can follow. Function

s String) As Boc arrays below ai

e only available in this procedure

Module-level private Procedure-level Module-level public

0 0

Post a comment