Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Set Variable to Nothing (VBA for Excel)

    I see many posts where the variables are set to nothing again at the end of the code! The latest post in the excel forum is <!post=this one,474566>this one<!/post> by <!profile=sdckapr>sdckapr<!/profile>. Is this very necessary to do, as I always assumed that all variables are emptied at the End Sub Statement anyways???
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    Object variables (the ones you assign using Set) are stored in a very different way from "simple" variables such as Integers or Booleans. Windows has to reserve space for all their properties etc.
    VBA performs the necessary garbage collection at the end of a procedure/function, but if an unhandled error occurs, this may fail, and the objects will remain in memory. I have seen examples in Access where code didn't work as expected because objects had not been not set to Nothing previously. So it is conventional wisdom to add error handling to all procedures / functions that use object variables, and to make sure that all object variables are set to Nothing even if an error occurs.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set Variable to Nothing (VBA for Excel)

    That does make sense. I can understand that that this would be conventional in an error handler, but Steve (and many others) use it outside of an error handler...(see link in previous post!). Why use it like this when End Sub will clear all variables in the very next line...(Is this just a case of habbit (being a good one), or being over cautious??). These lines will never be reached if an error occured further up the code??

    PS: Just querying this to get clarity...I'm not arguing the point!!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    For me, and I assume for several others frequenting these forums, it is a matter of habit - it won't hurt.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set Variable to Nothing (VBA for Excel)

    OK. Thx. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    I'm rather, whatchamacallit, finicky in these things <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    - I always indent code properly, even if it is a one-off.
    - After typing a line such as For i = 1 To 10, I always type the corresponding ending (Next i) before typing the code that goes within the loop.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set Variable to Nothing (VBA for Excel)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>... This is a good finicky to be in. (I must admit that I have OFTEN had a run time error because of un-ended statements, esp. End If!!!)
    And I am one person to have being "repremanded" by you finickyness and learned good lessons by it....so keep it up Your whatchamacallit Highness! <img src=/S/king.gif border=0 alt=king width=21 height=22>
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    > I have seen examples in Access where code didn't work as expected because objects had not been not set to Nothing previously.

    And I have seen examples in Fortran/COBOL/ALGOL/APL/ ....where code didn't work as expected because STUFF had not been not set to the-appropriate-null-value previously.

    I think it's a good programming habit to initialize every variable; execution time is negligible.
    I'm somewhat doubtful of doing garbage collection for the run-time routines.
    I am concerned about readablity of code, because I believe that it's the cost in human time that impacts development projects. Too many lines of housekeeping can distract my attention from the real purpose of the code.

    I suspect we will find a balance, that there are procedures which can be shown NOT to need initialization to null, and can be shown NOT to need finalization to null. Presumably only if a procedure can be guaranteed to execute to completion, or noty be interruptible, can we abandon garbage collection.

    That said, I just KNOW I'm going to run into problems if I don't take care with me new objects that are non-visible applications!

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set Variable to Nothing (VBA for Excel)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>... Well said, but what the heck did you say? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    >but what the heck did you say?
    Sorry, I've still had only one coffee,

    1) Too much clutter in housekeeping code can distract the reader, and that makes for problems (clean concise readable code is good).
    2) Even so, there are penalties when one creates application objects (that sit there, visible or not on the Windows task bar) and doesn't get rid of them when they are done.

    I think all in all :
    I just can't see the economic($) sense in taking care of the garden-variety of variables - Long, String, Boolean etc
    I can see sense in tidying up after the big boys such as Application Objects
    I'm ambivalent on intermediate things such as Structures, Dictionaries, Fields, Tables etc.

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

    Re: Set Variable to Nothing (VBA for Excel)

    The rule of thumb is that any object variable (initialized with the Set keyword) needs to be destroyed. Not doing so can result in the infamous memory leaks from your application. While those objects are *supposed* to be destroyed when they go out of scope, it doesn't always happen. Then you get weird behavior like an application refusing to shut down entirely. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> And BTW, concise and readable are sometimes mutually exclusive. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Charlotte

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    > The rule of thumb is that any object variable (initialized with the Set keyword) needs to be destroyed.

    I'll buy this. My guess is that the usual sloppy coding practices abound in the various teams that wrote object handing, but that the garden-variety of data was done properly.

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    I'm not sure how it works in VB, but in C++ it quickly becomes very clear why it's necessary to properly destroy objects that contain pointers to other objects. Without explicitly writing a correct destructor (roughly equivalent to setting object references to Nothing) it's quite easy to end up with a pointer refering to an object that no longer exists -> crash.

    Alan

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    > it's necessary to properly destroy objects

    I agree. I think confusion reigns in VBA because of aberrations in MSot's implemennation.

    The referenced article (below) says that it's not necessary to set to Nothing; VBE does it for you. Trouble is VBE doesn't always do it properly, so some of us decide to Set to Nothing on principle.

    Explict Set Nothings are guaranteed to work; in VBA they ought not to be necessary.

  15. #15
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Set Variable to Nothing (VBA for Excel)

    I admit to being pretty fastidious myself about setting object variables to Nothing. But in case anyone's interested, here's a cutely-titled contrarian view from VB guru Mark Hurst: Much Ado About Nothing.

Page 1 of 2 12 LastLast

Posting Permissions

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