vba static variable code example

Example 1: vba class static

' Import as a file MyStaticClass.cls:
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "MyStaticClass"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private mStaticCol As New Collection

Public Sub Push(ByVal pIndex As String, ByVal pValue As String)
Attribute Push.VB_Description = "Pushes a new value into StaticCol."
    mStaticCol.Add pValue, pIndex
End Sub

Public Property Get Element(ByVal pIndex As String) As String
Attribute Element.VB_Description = "Returns the element."
    Element = mStaticCol.Item(pIndex)
End Property
'--------------------------------------------------------------
' In a module:
Sub TesMe()
  With MyStaticClass        'No instanciation, mStaticCol is shared
        .Push "Jay", "circle"
        Debug.Print .Element("Jay")
    End With
End Sub

Example 2: vba static variable

' Static variable inside a function
Function MyStaticFunction(ByVal pToAdd As Integer) As Integer
    Static iStatic As Integer       ' Keeps its value between calls
    iStatic = iStatic + pToAdd
    MyStaticFunction = iStatic
End Function
'---------------------------------------------------------------------
Sub TesMe()
  Debug.Print MyStaticFunction(1)	' 1
  Debug.Print MyStaticFunction(3)	' 4
End Sub