Non-Functional Variable Scope (Static)

**SidewinderAM9M** · 06-26-2013, 09:38 PM

I have tried for hours to determine a single instance where the declaration of a static variable would be warranted and have not been able to do so. Here is what I have learned:

(1) A variable cannot be declared as static outside of a procedure.
(2) A variable's value remains after a procedure ends if the variable is declared outside of a procedure.
(3) A variable's value remains after a procedure ends and is available to other modules if the variable is declared outside of a procedure and is declared 'public'.
(4) A variable's value does not remain (at least for use in the immediate window, for other procedures in the same module, for other procedures in different modules, or for cells in a workbook) if it is only declared within a procedure.
(5) A variable's value is set to a default value upon initial declaration and reset upon subsequent declarations.

According to what I have read, a variable should be declared 'static' if it is desired to declare the variable within a procedure such that the variable retains its value after the procedure ends. However, I have found (4) to be true regardless of how a variable is declared within a procedure, and regardless of any 'module wide' options such as 'option explicit', 'option private module', and/or the like.

My 3 questions are:

(a) Is there any scenario in which a 'static' variable declared within a procedure has functionality that is only present by such a declaration?
(b) Why would I want a variable to retain its value after the end of a procedure if I cannot use that retained value by any other procedure or excel function; am I missing something that could use the variable?
(c) If the answers to (a) and (B) are negative, then why does the 'static' variable declaration exist?

Thanks in advance to the capable programmer who can both understand and willingly answer my questions.

**Kyle123** · 06-27-2013, 01:46 AM

It's got to be said, you can get by quite happily and never use them; there's also an argument against using them altogether, ultimately, it's to do with the scope of the variable, I live by making scope as small as possible. It can be useful to keep a variable alive for re-use after a sub/function has run; but doesn't need to be accessible from elsewhere. For example if you use an object that can take a long time to create, you don't want to create it everytime you run a function, but that object doesn't need to be accessible from outside the function, something like:

Please Login or Register  to view this content.

For the above, you could use a module level declaration and it would do the same thing, but I find that a bit messy if you're never going to use it anywhere except the single function - and it goes against keeping scope as small as possible

**SidewinderAM9M** · 06-29-2013, 09:56 PM

Kyle123,

I am incredibly satisfied with that answer. I was baffled because I could not get a separate procedure or function to use a variable that was supposedly preserved by declaring it static. I was definitely on the wrong track. The value of a static variable is that the same procedure or function, not a different one, can re-use that variable after it ends without having to re-calculate its value. This saves time by preventing the necessity of recalculating the value of the variable multiple times, but keeps the scope much smaller than declaring it module-wide. Thanks, your reply is much appreciated!

**shg** · 06-30-2013, 12:08 AM

(b) Why would I want a variable to retain its value after the end of a procedure if I cannot use that retained value by any other procedure or excel function; am I missing something that could use the variable?

Kyle's example is the obvious case that I frequently use.

Another example is a random number generator that retains the last value as the seed for the next value.