Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Public Variables (English, Excel 2k SR-1)

    Hello --

    This might be a very basic query...

    I have declared a few public variables in a separate module.
    These are used across multiple procedures as and when they are executed.

    My query --
    After the procedures finish executing, is the memory utilised by the public variables 'released' ?
    From what I have read in the VBA on-line help, the 'scope' of variables is terminated once the procedure finishes executing, and the variables do not 'retain' their values.

    But when I check the current value of a 'used' public variable (after the procedure is over),
    by using MsgBox <public_variable_name>,
    the MsgBox displays the last assigned value of the variable !

    Can someone please clarify...

    Confused... :-|
    Khushnood

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Variables (English, Excel 2k SR-1)

    Local variables, that is variables declared within a procedure, are released when the procedure ends, although you have to be careful about object variables, which may not release their resources unless set to Nothing. Global/Public variables have an application level scope, which means they will hold their value (and hold onto resources) until they are set to Nothing or the application closes. They also get reset when an unhandled error occurs in code, but globals are resource-intensive, which is one of the reasons I prefer not to use them. A better idea is to write "loosely coupled" routines that require arguments and return values. That kind of code will work even if dropped into another project (or possibly even another application) because it isn't dependent on public variables.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Public Variables (English, Excel 2k SR-1)

    Charlotte,

    Your comment re globals being resource-intensive caught my attention. In my firm's global templates (which I didn't design<g>) there are nearly 200 global variables and constants (all strings) (probably a lot of them aren't even used/needed) . In addition a document automation project I'm working on may add another 100 (also strings).

    This sounds like a lot if there is an issue with resource use. Do you know of any way to quantify the load on resources these might impose? (Word 2000, Windows 2000 on Windows Terminal Server fwiw)

    Gary

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Variables (English, Excel 2k SR-1)

    Well, you could do the math by adding the number of bytes required for each datatype to see how much RAM is being allocated to those globals, but it will only make a noticeable difference on a slow machine with minimal RAM. If you're running Terminal Server, I have no idea of the implications of globals. I don't like them for a number of reasons, mainly because they break on unhandled runtime errors, may be reset unexpectedly by other routines, and result in more tightly coupled code that will break in an environment where the globals don't exist.

    I prefer loosely coupled code that gets its values passed in as arguments. That way you always know the called routine is getting the values you want it to have, and the code is more portable. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Variables (English, Excel 2k SR-1)

    Public variables retain their value until the Project is closed or the variables are set to Nothing.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Variables (English, Excel 2k SR-1)

    Except when an unhandled error destroys all their values.
    Charlotte

Posting Permissions

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