Use of Static Statement

[MWE]

Per VBA Help regarding the Static statement:

Used at procedure level to declare variables and allocate storage space. Variables declared with the Static statement retain their values as long as the code is running.

From this I would conclude that once the code stops running, variable values are "lost" and if the code is run again, static variables are reset to "zero". But that does not seem to be the case. Consider this test example:

Sub TestStatic()
    Dim I As Integer
    Dim J As Integer
    For I = 1 To 5
        Call StaticSub(J)
    Next I
    MsgBox J
End Sub
Sub StaticSub(J)
    Static Counter As Integer
    Counter = Counter + 1
    J = Counter
End Sub

The first time I ran TestStatic, the display was "5". The second time it was "10" and so on. Counter is not resetting even though the code HAS stopped running. If I force an error leading to an abnormal end, the next time I run TestStatic, the display is again 5.

So how do I force Counter back to zero each time I run the parent application?

[Jacob Hilderbrand]

I would do this.

Option Explicit
 
Dim Counter         As Long
 
Sub TestStatic()
Dim i               As Long
Dim j               As Long
Counter = 0
    For i = 1 To 5
        Call StaticSub(j)
    Next i
    MsgBox j
End Sub
 
Sub StaticSub(j)
Counter = Counter + 1
    j = Counter
End Sub

[Zack Barresse]

I've read that before in the Help files as well and found the same issue. The fact is that it will retain the value even though the code has stopped running. It should lose the value when the project is closed though.

[Jacob Hilderbrand]

Also if you click Reset button in the VBE the values are all reset.

[MWE]

I would do this.

Option Explicit
 
Dim Counter         As Long
 
Sub TestStatic()
Dim i               As Long
Dim j               As Long
Counter = 0
    For i = 1 To 5
        Call StaticSub(j)
    Next i
    MsgBox j
End Sub
 
Sub StaticSub(j)
Counter = Counter + 1
    j = Counter
End Sub

Thanks for the idea. Unfortunately, Counter must be inside the child proc.

[MWE]

I've read that before in the Help files as well and found the same issue. The fact is that it will retain the value even though the code has stopped running. It should lose the value when the project is closed though.

Thanks. Unfortunately, the parent procedure could be run many times before the project is closed.

[MWE]

Also if you click Reset button in the VBE the values are all reset.

This is an interesting idea. Is there some way to click the VBE's Reset button programmatically?

[TonyJollans]

You can use the End statement to clear all variables when you finish running ..

Sub TestStatic() 
         Dim I As Integer 
         Dim J As Integer 
         For I = 1 To 5 
                 Call StaticSub(J) 
         Next I 
    MsgBox J 
    End
End Sub 

Sub StaticSub(J) 
    Static Counter As Integer 
    Counter = Counter + 1 
    J = Counter 
End Sub

Sorry about the layout - I don't know how to control the layout inside the VBA tag

[Bob Phillips]

This is an interesting idea. Is there some way to click the VBE's Reset button programmatically?

You could try

Application.VBE.CommandBars.FindControl(ID:=228).Execute

[MWE]

You can use the End statement to clear all variables when you finish running ..

Sub TestStatic() 
         Dim I As Integer 
         Dim J As Integer 
               For I = 1 To 5 
                            Call StaticSub(J) 
     Next I 
     MsgBox J 
     End
End Sub 

Sub StaticSub(J) 
     Static Counter As Integer 
     Counter = Counter + 1 
     J = Counter 
End Sub

Sorry about the layout - I don't know how to control the layout inside the VBA tag

ou could try

Application.VBE.CommandBars.FindControl(ID:=228).Execute

thanks to both of you. Both methods work.