Coding Convention Questions #2 (To clean up or not to clean up)

Here's the 2nd question. I have come across conflicting ideas on cleaning up variables before exiting a procedure.

Suppose we have a sub like so:

Please Login or Register  to view this content.

Now the 3 schools of thought I am comparing:

1. Forget about cleaning up. VBA automatically scraps all procedure level variables when the sub ends, you don't need to waste effort and code bloat your procedure by carefully setting all procedure variables to vbNull/vbNullString/Nothing
2. You MUST clean up all procedure variables before ending the sub (Then hint darkly at possibly dangerous vague consequences apparently caused by VBA not always cleaning up after itself?)
3. If you have objects in your sub then you really should erase the objects. All other procedure level variables can be ignored (they don't need to be erased/reset).

So. I'm confused. Can anyone throw some light on this?

I know I don't always clean everything, but I defintely close anything I open....especially database connections....

I will agree with onedaywhen here: http://stackoverflow.com/questions/1...-vba-pros-cons

Thank you to those who have responded.

Is there anyone prepared to put their hand up for 2? If so, I really would be interested in hearing from you as to the whys and wherefores.

Because I have these conflicting doubts as to whether 2 is the only real choice or whether 2 is merely a tradition that has been passed on from other languages (where it is necessary).

#2 was required with DAO, as stated in earlier link. This created theory that you must always do this.

Edit: I misread - that is #3. #2 is nonsense.

I am adopting method 3 - I will close all database connections & set all objects to nothing (and not worry about erasing arrays & setting variables to vbnull/vbnullstring)

I am marking this thread as solved. If anyone can think of any situation where you would need to do more than this - please post to this thread. I'd like to hear more.

Originally Posted by Marc L

Hi,

have you read the VBA help of End statement ?

I haven't read the help but I have an idea what End does.
(Reads help)
Not intending to be rude - what point are you making?

Originally Posted by Marc L

Read help and I guess you'll understand ! (for a main procedure …)

But I just did.

If your point is that the End Function or End Sub resets all local & module level variables - I already knew that it is meant to do that.

I can't be sure that VBA resets 100% of variables 100% of the time. VBA (and Excel for that matter) have their share of bugs.

End Function or End Sub resets all local & module level variables

Local variables, not module-level variables.

Originally Posted by shg

Local variables, not module-level variables.

You are right. If the procedure in question is called from another procedure then reaching End Function or End Sub in the called procedure does not cause module level variables to get reset (unless of course the caller sub is in another module).

Originally Posted by Marc L

Neither End Function nor End Sub but the End statement alone in a main procedure !
Like the last code line in this post …

I didn't know which End statement you were referring to earlier (the Help covers all the End statements). My fault, sorry.

Re: stand-alone version of End - how is this an argument for using method #2?

http://www.vbforums.com/showthread.p...%29-a-bad-idea

With this I agree.

Originally Posted by Marc L

yes, avoid it !

With this also I agree.

I do not see any necessity to use End - there are better ways to clean up resources. Especially if you will use Ribbon code.

Originally Posted by Marc L

But 'cause sometimes I'm lazy,

This I understand! But question refers to good coding convention, I think.

Originally Posted by Marc L

End statement cleans all and even closes opened files, what else ?‼

Regards !

Thanks Marc but that is what End does. My question was more 'Why would you need to?'