Static Variables

Variables can have a lifetime.
The word "static" indicates that the local variable is preserved between calls.
Static variables are not used very often but they can be very useful when used in the correct way.
A static variable can be thought of as a local variable with memory.
A static variable is a local variable whose lifetime is the lifetime of the entire module and not the procedure where it is declared.
In fact static variables retain their values as long as the code module is active. There does not have to be any code running all the time.
Therefore a static variable has the scope of a local variable but the lifetime of a module level variable.
Makes a variable persistent even after the procedure has completed.
The next execution of the routine can access the previous value.
It is the lifetime of a variable that determines its existence, the scope determines its visibility.
A static variable is defined within a procedure and although it has procedure-level scope it actually has module-level lifetime
This means that you can only use the variable inside the procedure in which it is declared but it value is maintained between calls to the procedure
You can also declare a procedure as Static in which case all the variables declared inside the procedure are treated as static.

Private Module Level Variables

You can obtain the same effect using a private module level variable instead of a static variable.
It is considered better programming practice to use static variables as this uses a more restictive scope.
This helps to prevent accidental changes being made to the variable in other portions of the code.

Example

Using "static variables" lets you retain the value of a variable that may go in and out of scope during execution, yet remain valid.
A static variable has the scope of a local variable although the lifetime of a module level variable.

Public Sub Procedure_One() 
   Debug.Print Function_Two() 
   Debug.Print Function_Two() 
   Debug.Print Function_Two() 
   Debug.Print Function_Two() 
   Debug.Print Function_Two() 
End Sub 

Public Function Function_Two() As Integer 
Static iValue As Integer 

   iValue = iValue + 1 
   Function_Two = iValue 
End Function 

It is possible that a variable can go in and out of scope and yet remain valid during that time (ie keep its value).
Once the lifetime of the variable expires the value is lost.
It is the lifetime that determines the existence of a variable and it's the scope that determines its visibility.
Indicate that the variable is initialised the first time and is then preserved between function / procedure calls.
A static variable is a local variable whose life time is the lifetime of the entire module, not just the procedure

Static Procedures and Functions

To make all local variables in a procedure or function static, place the static keyword at the beginning of the procedure or function heading (eg Static Sub or Static Function).

Private Static Sub Procedure_One() 
Dim slocalvariable As String 

End Sub 

Initialising

Function myStaticFunction( _ 
   Optional ByVal bResetValue As Boolean = False, _ 
   Optional ByVal iStartValue As Integer = 0) As Integer 

Static myStatic As Integer 

   If (bResetValue = True) Then 
      myStatic = iStartValue 
   End If 
   myStatic = myStatic + 1 
   myStaticFunction = myStatic 
End Function 

Sub RunThis() 
   Debug.Print myStaticFunction(True,0) 
   Debug.Print myStaticFunction() 
   Debug.Print myStaticFunction() 
   Debug.Print myStaticFunction() 
   Debug.Print myStaticFunction() 
   Debug.Print myStaticFunction() 
End Sub