+ Reply to Thread
Results 1 to 4 of 4

Non-Functional Variable Scope (Static)

  1. #1
    Registered User
    Join Date
    05-04-2013
    Location
    Pleasant Hill, CA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Non-Functional Variable Scope (Static)

    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.
    Last edited by SidewinderAM9M; 06-26-2013 at 09:42 PM. Reason: Resolution of Formatting Issues

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Non-Functional Variable Scope (Static)

    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

  3. #3
    Registered User
    Join Date
    05-04-2013
    Location
    Pleasant Hill, CA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Non-Functional Variable Scope (Static)

    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!

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Non-Functional Variable Scope (Static)

    (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.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1