Whether or not you can use a variable within a particular procedure depends on where and how the variable was declared, that is, the scope of a variable. Scope is the term given to the visibility of a variable, in other words, where it can be seen from. A variable that is created within a procedure can only be seen, and therefore can only be changed, from within that procedure. Its scope is local to the procedure. However, a variable can also be declared outside a procedure in the (General) (Declarations) section. In this case, it can be seen by all procedures in that module (and sometimes other modules as well). Thus, its scope is either private (to the procedures only in the local module) or public (to procedures in all modules ).
The lifetime of a variable is defined as how long the variable can be seen for; in other words, how long it will contain the value assigned to it. Normally, local variables 'live' for as long as their procedure has the control so when the procedure ends the variable ceases to exist. The next time the procedure is called, the local variables are recreated.
If you want local variables to exist even when the procedure exits, you can use the Static keyword. Making a variable static ensures that its contents are not lost. You can also declare a procedure as Static , which makes all of the local variables within the procedure Static . However, static variables still have the same scope as non-static variables. They cannot be seen outside of their procedure, even though their lifetime is longer than the procedure.
A Public variable exists as long as the database is open and retains its contents throughout the life of the program.
With VBA, you can apply scope to the procedures themselves by using the Public and Private keywords. A Private procedure can only be called from within the module in which it is declared, whereas a Public procedure can be seen from everywhere.
A procedure has to be Public for it to be called from the Immediate window. A Private procedure is only visible to other procedures in the same module. So if you are creating Private procedures and you need to test them from the Immediate window, you will have to change them to Public to test them. Don't forget to change them back though, once you've finished testing.
Let's look at these concepts in more detail.
You have already seen local variables in the VariantExample procedure and others that you created earlier. Remember that the variables are local to the procedure, no matter where the procedure can be seen from. So local variables in a Public procedure have the same scope as local variables in a Private procedure it is just the procedure that can be seen from outside, not the variables. This means that you can have different procedures with local variables that have the same name .
Let's have a look at some simple examples that illustrate this.
Create a new subroutine and call it Procedure1 . Make sure it's a Public procedure. Enter the following lines:
Public Sub Procedure1() Dim intVariable1 As Integer Dim intVariable2 As Integer intVariable1 = 1 intVariable2 = 2 Debug.Print intVariable1 Debug.Print intVariable2 End Sub
Now create another subroutine, a Private one this time, called Procedure2 , with these lines:
Private Sub Procedure2() Dim intVariable1 As Integer intVariable1 = 100 Debug.Print intVariable1 End Sub
Now you need a third subroutine (another Private one) called TestLocal , that calls the first two:
Public Sub TestLocal() Procedure1 Procedure2 Procedure1 End Sub
Now open the Immediate window and run the TestLocal subroutine:
As you can see, the variables in Procedure1 are only seen in that procedure. Likewise with the variable in Procedure2 . With local variables, you can have the same variable name in different procedures without them affecting each other.
Now try accessing intVariable2 from within Procedure2 by adding the following line before the End Sub :
Before running the example again, think about what you expect to happen.
Now try typing TestLocal in the Immediate window again. Were you surprised at receiving the error? Whether yes or no, the reason is simple. Procedure1 declared intVariable2 as local. Therefore, no other procedure can see the variable. Don't forget to remove this last line, otherwise it will cause error messages later.
Now let's look at another example to explain local scope.
Stop the module running by selecting the Reset button on the toolbar (the one that's a filled in square). Then create another Public subroutine, TestLocal1 , and add the following lines:
Public Sub TestLocal1() Dim intVariable1 As Integer intVariable1 = intVariable1 + 1 Debug.Print intVariable1 End Sub
Here, you are adding 1 to the variable even though it has not been used before. This is allowed, since an integer is set to when first declared. This should give you a clue as to what happens when you run the program.
Now type TestLocal1 in the Immediate window. Now type TestLocal1 again. You should see:
You can see that intVariable1 is reset each time the procedure is called.
Local variables only exist while a procedure has the control. When the procedure ends, local variables 'die'. When the procedure is called again, local variables are reset.
To allow a variable to retain its value over multiple calls, you must declare it as Static . This means that the variable is only initialized once the first time the procedure is called. To declare a variable in this manner, you replace the Dim with Static :
Static intVariable1 As Integer
Create another subroutine called StaticScope . Add the same code as before, but this time change the variable declaration line from Dim to Static :
Public Sub StaticScope() Static intVariable1 As Integer intVariable1 = intVariable1 + 1 Debug.Print intVariable1 End Sub
Run the subroutine several times from the Immediate window.
So, just by changing one word, you have dramatically altered the way the program works. You'll find this a useful feature; for example, you can use static variables to create a function that keeps a running total each time a new value is passed in, or one that keeps track of the number of times it has been called. This could be used, for instance, as a user name and password system for securing a system. You could limit the user to three tries before shutting the system down.
If you want all variables in a procedure to be static, just put Static before the procedure declaration:
Static Sub AllVariablesAreStatic ()
All variables within that procedure will now be Static irrespective of whether they are declared with Static or Dim .
If you need other procedures to see your variables, you can use a global variable, defined in the (Declarations) section of a module. These are known as module-level variables. There are two types of global variables:
Those that can be seen by every procedure in the module but can not be seen outside of the module. These are module-level private variables. You declare these using the Private keyword.
Those that can be seen everywhere in the program, even outside of the module. These are called public variables. You declare these using the Public keyword.
We will create an example here to clarify the principle of a module-level private variable.
We'll create two subroutines called TestProc1 and TestProc2 within the same module that you've been using throughout this chapter.
Move to the (Declarations) section of the module window and enter the following line under the Option statements:
Private intModuleVariable As Integer
Now add the code to the new subroutine, TestProc1 , as follows :
Public Sub TestProc1 intModuleVariable = intModuleVariable + 1 End Sub
Now add the code to the new subroutine TestProc2 , as follows:
Public Sub TestProc2 Debug.Print intModuleVariable End Sub
Try calling the two procedures from the Immediate window to see what happens:
Here you can see that although neither procedure declared intModuleVariable , they can both access it. However, procedures in other modules or forms will not be able to access it.
A module- or form-level variable has the scope of the module or form. If, for example, you declare a variable in the (Declarations) section of a form, all procedures in that form will have access to it. When the form is unloaded, the variable and its contents will die and will be reset when the form is next loaded.
Now add a third new procedure TestProc3 , as follows:
Public Sub TestProc3 Dim intModuleVariable As Integer intModuleVariable = 100 Debug.Print intModuleVariable End Sub
This declares intModuleVariable as a local variable when you already have it as a module-level variable.
Run through the procedure again to see what happens:
The call to TestProc3 does not change its own (separate) variable intModuleVariable , but also does not affect the public intModuleVariable variable. Only its own copy is in scope within the procedure.
So, not only can you have procedures with local variables of the same name, but you can also have local variables with the same names as public ones. In this case, the procedure with the local variable will always use its own variable, rather than the global one. You can probably see that it is not a very good idea to create local and public variables with the same name, as it can lead to confusion when looking at the code later. It is much better practice to use different names for your variables.
A public variable is one that can be accessed from all modules and forms. They can only be created in modules, not within the (Declarations) section of a form. To declare a public variable, replace the Private keyword with Public :
Public intModuleVariable As Integer
This variable is now accessible to all modules and forms and has a lifetime until the database is closed.
You cannot declare Public variables within a procedure.
There are some general principles that you should bear in mind when deciding on the scope and lifetime of your variables:
Unless you are writing a general routine, make procedures private. This is especially true if you are developing in teams or writing add-ins, as then there is no danger of your procedure clashing with another of the same name. However, you should never really have more than one procedure with the same name. The names of procedures should actually be decided at the design time, specifically to avoid this sort of confusion.
Wherever possible, you should avoid manipulating variables that are declared outside a procedure. Instead, pass the value of the variable into the procedure using an argument. Inside the procedure, you can perform the calculations required and return the new value to the code that calls it. This not only protects you from errors that are difficult to find, but also makes your procedures far more generic and reusable.
Use public variables sparingly and with care. You will generally find that only a small number of public variables are used in most programs. It is sometimes much easier to use a public variable than to have the same variable declared in several procedures and passed as a parameter. However, do not always take the easy route. Think about how a variable is going to be used and try to plan ahead.