Set Variable to Nothing (VBA for Excel)

Topic

New Reply

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 this one by sdckapr. Is this very necessary to do, as I always assumed that all variables are emptied at the End Sub Statement anyways???

Reply | Quote

Replies

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.

Reply | Quote

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!!

Reply | Quote

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

Reply | Quote

OK. Thx.

Reply | Quote

I’m rather, whatchamacallit, finicky in these things
– 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.

Reply | Quote

… 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!

Reply | Quote

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.

Reply | Quote

That link appears broken. Here’s the text (attached)

(later) >> I just mis-posted it.

OK. I’ve

Set Attachment=Nothing

Reply | Quote

Actually, the link’s still good, I just mis-posted it. (I have now fixed my post.)

Reply | Quote

Thanx for the link that has lots to do about nothing… . Its very informative.

AND

Thanx to all who posted info about nothing! I really have been overwhelmed with your info about making nothing of variables! Very informative!!!
Cheers All

Reply | Quote

> 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!

Reply | Quote

… Well said, but what the heck did you say?

Reply | Quote

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

Reply | Quote

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. And BTW, concise and readable are sometimes mutually exclusive.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

That’s pretty much the way it works in VB/VBA too. Object variables hold pointers to the objects or to the address of the object. When you set the variable to nothing, you are making sure that those pointers are released.

Reply | Quote