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.
Bookmarks