Global and Static Variables

As stated earlier, a variable is only visible within the procedure and will only exist until the procedure completes its job. There are two exceptions—global variables and static variables.

Global Variables

You will recall that the code at the very top of the module is the general declarations area. What goes there affects the whole module. Up to this point, we have only used two lines of code there:

Option Compare Database Option Explicit

For the sake of review, Option Compare Database forces VBA to use the Access sort order when comparing strings. You could also set it to Option Compare Binary. This, in essence, will make strings case sensitive when comparing two strings. It would look upon the strings “Test” and “test” as being unequal.

Option Explicit means that a variable must be declared before it can be used. As I have pointed out earlier, it is a good idea to use this. It will make for fewer potential programming errors.

Interestingly, these are not the only things you can do in the declarations area. You can also set a variable there. The variable will be available to all the procedures in the module. As an example, you could set the following global variable:

Private strFullName As String

Notice the example is set as Private. As with procedures, Private means it can only be seen by procedures in the module. Public means it can be accessed by other modules. In my many years of programming, I have never set a global variable as Public. Again, it is strongly recommended that your variables should only be accessed through procedures. By not doing that, anyone can put any value into a variable that they want. That is not a very secure way of doing things.

Static Variables

A static variable is one that will stay in memory after the procedure has completed its job. However, it can only be accessed by that procedure.

As an example, the procedure may need to keep a running total of a count. Every time the procedure runs, it may need to add to that total. For that, a static variable does the job quite nicely.

Let’s take a quick look at a static variable. Set up a procedure as follows:

Sub staticTestProcedure()   Static intCounter As Integer   intCounter = intCounter + 1   Debug.Print intCounter End Sub

Notice that intCounter is declared as being Static. Again, this means that intCounter is still local to this procedure. No other procedure will have access to it. However, when the procedure is finished, intCounter will be retained in memory with the last value assigned to it.

If you run the procedure multiple times from the Immediate window, intCounter will increment to the next number each time.

Neither global nor static variables are used frequently. However, it is a good idea to have them in your collection of tools for special occasions.