PDA

View Full Version : [SOLVED] Use of Static Statement



MWE
08-23-2005, 11:09 AM
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
08-23-2005, 11:18 AM
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
08-23-2005, 11:25 AM
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
08-23-2005, 11:27 AM
Also if you click Reset button in the VBE the values are all reset.

MWE
08-23-2005, 01:49 PM
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
08-23-2005, 01:50 PM
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
08-23-2005, 01:52 PM
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
08-23-2005, 02:11 PM
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
08-23-2005, 02:20 PM
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
08-23-2005, 02:27 PM
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.