VBA/Excel/Access/Word/Language Basics/Static variable

If you need a procedural level variable to retain its value between calls to the procedure, declare the variable using the Static keyword

 
Private Sub MyProcedure2()
     Static myVar As Integer
     myVar = myVar + 1
End Sub

Redefine static variable

 
Sub Scope1()
  Static Sales
  Sales = Sales + 1
  MsgBox Sales
End Sub
Sub Scope2()
  Static Sales
  Sales = Sales + 10
  MsgBox Sales
End Sub

Static variable lifetime

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

Static Variables: A Special Type of Local Variable

 
Private Sub cmdLocalAge_Click()
  Dim intAge As Integer
  intAge = intAge + 1
  Debug.Print intAge
End Sub
Private Sub cmdStaticAge_Click()
  Static sintAge As Integer
  sintAge = sintAge + 1
  Debug.Print sintAge
End Sub