Variables have both a scope and a lifetime. Understanding the concepts of scope and lifetime are often critical to writing code that works as you expect.
It is common practice to use naming conventions to add meaning to your variable names. One convention, of course, is to not use any convention at all. Another convention is to append a one- to three-letter prefix to your variable names to help remind you of the variable's data type. Some programmers also may prefix their module level variables with an "m" and their global variables with a "g" in addition to the data type prefix.
I have tried many different conventions over the years and have settled on the following. For the most common data types, I use a single letter to denote the data type. For the less common basic data types, I use two letters. Finally, I prefer to prefix module level variables with an "m".
Dim nMyInteger As Integer
Dim mnMyModuleLevelInteger As Integer
Dim sMyString As String
Dim lMyLong As Long
Dim vMyVariant As Variant
Dim cMyDollar As Currency
Dim dtMyDate As Date
Dim dbMyDouble As Double
Naming conventions are really a personal preference. I recommend that you find something that works for you and then use it consistently. In some cases, your company may already have conventions that you are required to use, in which case you may not have any choice.
Variable scope refers to the breadth of a variable's visibility and is determined by the location of the variable's declaration or the use of the Public or Private keywords. You have three possibilities when you are determining a variable's scope: procedural scope, module scope, and global scope.
Variables declared within a procedure are local to the procedure only and are referred to as procedural-level variables. You can only access procedural-level variables with code that resides in the procedure in which the variable was declared.
Variables can also be declared at the top of a module as either module-level variables or global variables. If you declare a variable at the top of a module, the variable is a private or module-level variable by default. This means that only code that resides in the same module that contains the variable declaration can access or use the variable. If you replace Dim with Public, you can create a global variable. Global variables can be used by any code in the same project. Consider the following three variable declarations placed at the top of a module.
Dim msMessage As String ' A module level variable
Private msMessage2 As String ' A module level variable Public gsMessage As String ' A global variable
The first two declarations perform the same task. They declare module-level variables. The second declaration, using the Private keyword, is preferred in that it explicitly makes your intentions clear. The third declaration creates a global variable. Note that the variable names are prefixed with an "m" if the variable is a module-level variable and a "g" if it is a global variable. This convention is a matter of personal preference. I like the fact that, if used consistently, when you see the variable being used in a procedure, the "m" or "g" prefix gives you a clue as to where to look to find the variable's declaration.
The lifetime of a variable refers to the period of time from which the variable is available for use in your code to the period of time in which the variable is removed from your computer's memory.
For procedural-level variables, the variable exists from the moment the procedure begins executing until the Exit/End statement is executed. The next time the procedure executes, a brand new set of variables are created. That is, the value of each variable is not preserved between runs. This rule has two exceptions: the first is for subroutines declared using the Static statement, the second is for variables declared using the Static statement. In each of these two exceptions, the lifetime of a variable begins the first time that procedure is executed and ends when the workbook that contains the procedure is closed. You can use the following two procedures to experiment with static variables. The first routine uses the Static keyword in the procedure declaration. The second routine uses the Static keyword in the declaration of the procedure level variable.
Static Sub TestStaticRoutine() Dim x As Integer
Sub TestStaticRoutine2() Static y As Integer
To experiment with these, perform the following steps.
1. Copy the code above into a module.
2. Put the cursor in the procedure you'd like to run and press F5 to execute the code.
3. Rerun each procedure a few times and you'll see the applicable variable increment with each run.
4. Save and close the workbook.
5. Reopen the workbook and then rerun the procedures.
For module-level variables, the variable exists from the moment the workbook containing your code opens until the workbook is closed. The value of a module-level variable behaves something like a static variable in the sense that once a value is given to it, the value remains in place until you change it or close the workbook.
Was this article helpful?