VBA: benefit of closing objects and setting to nothing?

Question

I've heard and read debates on whether or not it is necessary to close objects and set their object variables to nothing. Some online commenters have said that while it was essential in the past to do so (e.g., pre-2007, leaving a recordset open when Access closed would require Task Manager to terminate Access), memory is now handled much better --- the result of which is that if an object is no longer needed in a procedure, the End Sub/End Function statement now does what "[object].Close" and "Set [object] = Nothing" previously did.

Besides allowing an object variable to be redefined or to prevent unintended changes to the object, is it essential or beneficial to close all objects and set their object variables to nothing before ending the sub or function when there is no further use for those objects? If no longer essential or beneficial, with which version of Office did that become the case? If it is essential or beneficial, why? Does doing so help reduce file-size bloat over time (due to temporary objects)? Thanks.

Answer 1

It's more important to be consistent, i.e. always manage your code the same way, regardless of whether Access might sometimes handle things anyway.

Answer 2

It's more important to be consistent, i.e. always manage your code the same way, regardless of whether Access might sometimes handle things anyway.

Hi George,

I agree that consistent code is very important. I would go a step further with stating that code should be as structured as data in a normalized database. So, no duplication of code, but re-usable code segments.

In all my applications I use the same code library to perform some 95% of all functionality.

But now back to the basic question: Is it necessary for an object, declared within a procedure, to set it to Nothing at the end of the procedure? Or is it some kind of "belief", with the device: if it does not help, it does not harm?

I have done many experiments on that point, but never could induce a hanging process based on just omitting the "Set object = Nothing" line.

Imb.

Answer 3

The principle I was trying to enunciate was this:

Train yourself to be consistent.