Scope and Lifetime of Variables

There are two important concepts associated with variables:

□ The scope of a variable defines which procedures can use that variable

□ The lifetime of a variable defines how long that variable retains the values assigned to it

The following procedure illustrates the lifetime of a variable:

Sub LifeTime() Dim Sales Sales = Sales + 1 MsgBox Sales End Sub

Every time LifeTime is run, it displays a value of one. This is because the variable Sales is only retained in memory until the end of the procedure. The memory Sales uses is released when the End Sub is reached. Next time LifeTime is run, Sales is re-created and treated as having a 0 value. The lifetime of Sales is the time taken to run the procedure. You can increase the lifetime of Sales by declaring it in a Static statement:

Sub LifeTime() Static Sales Sales = Sales + 1 MsgBox Sales End Sub

The lifetime of Sales is now extended to the time that the workbook is open. The more times LifeTime is run, the higher the value of Sales will become.

The following two procedures illustrate the scope of a variable:

Sub Scope1()

Static Sales

Sales = Sales +

1

MsgBox Sales

End Sub

Sub Scope2()

Static Sales

Sales = Sales +

10

MsgBox Sales

End Sub

The variable Sales in Scopel is not the same variable as the Sales in Scope2. Each time Scopel is executed, the value of its Sales will increase by one, independently of the value of Sales in Scope2. Similarly, the Sales in Scope2 will increase by 10 with each execution of Scope2, independently of the value of Sales in Scopel. Any variable declared within a procedure has a scope that is confined to that procedure. A variable that is declared within a procedure is referred to as a procedure-level variable.

Variables can also be declared in the declarations section at the top of a module, as shown in the following version of the code:

Option Explicit Dim Sales

Sub Scope1()

Sales = Sales + 1 MsgBox Sales End Sub

Sub Scope2()

Sales = Sales + 10 MsgBox Sales End Sub

Scopel and Scope2 are now processing the same variable, Sales. A variable declared in the declarations section of a module is referred to as a module-level variable, and its scope is now the whole module. Therefore, it is visible to all the procedures in the module. Its lifetime is now the time that the workbook is open.

If a procedure in the module declares a variable with the same name as a module-level variable, the module-level variable will no longer be visible to that procedure. It will process its own procedure-level variable.

Module-level variables, declared in the declarations section of the module with a Dim statement, are not visible to other modules. If you want to share a variable between modules, you need to declare it as Public in the declarations section:

Public Sales

Public variables can also be made visible to other workbooks, or VBA projects. To accomplish this, a reference to the workbook containing the Public variable is created in the other workbook, using Tools O References in the VBE.

0 0

Post a comment