I have just learnt (from serendipity) that if a variable is declared
global - PUBLIC either by way of a direct PUBLIC declaration or using
a DIM statement at module level, the variable so treated becomes
"static". Namely that, it exits the procedure it is worked upon with a
runaway value which is accessible by other procedures in the module.

The cut of my rude wakening is that, at any later stage (while the VBE
is open), if the "host procedure" is fired again, the "entry value" of
the variable takes on the last "exit value" . To illustrate,
supposing we have:

Dim x as Integer

Sub ArithSeries()
For i = 1 To 10
x = x + i
Next
MsgBox x
End Sub

The first run of the code will yield x=55, the second x=110, the third
x=165 etc. , the same way we would expect a "Static x as Integer"
local declaration behave ( with of course the one crucial difference
that the global integrity hold sway in the one and not the other).


While the foregoing characteristic of a global declaration can be
exploited for a purposeful end, it could be tamed to operate normally
without the "static" overlay by simply intializing the variable to 0 (
or empty string as the case may ) just prior to looping. The
normailzed version is:

Dim x as Integer

Sub ArithSeries()

x = 0
For i = 1 To 10
x = x + i
Next
MsgBox x
End Sub

This way, every run will faithfully produce a global x=55 result.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=394317