VBA: Setting objects to Nothing

[20 Ton Squirrel]

Hello all! This is a "best practice" sort of question for you fellow programmers out there.

Is it necessary to always set object variables to Nothing at the end of a procedure?

In a procedure where any sort of object variable is declared, I always make a habit of setting the object to nothing at the end of the procedure. An example:

Public Sub sampleProcedure()
  Dim wks As Worksheet
  Dim rng As Range
  Dim dct As Scripting.Dictionary

    Set wks = ThisWorkbook.Worksheets("Sheet1")
    Set rng = wks.Range("A1")
    set dct = New Scripting.Dictionary

    '«BRILLIANT AUTOMATION PROGRAMMING HERE»

HANDLER_EXIT:
    Set wks = Nothing
    Set rng = Nothing
    set dct = Nothing

    Exit Sub

HANDLER_ERROR_SOMETHING1:
    '«BRILLIANT ERROR HANDLING HERE»

HANDLER_ERROR_SOMETHING2:
    '«BRILLIANT ERROR HANDLING HERE»

End Sub

I do this to make certain that the memory is being released for that particular variable. Doesn't matter if it is a range, worksheet, collection, or some wild class object... I always set it to Nothing.

Am I being OCD or is this a valid concern?

 

[NdNoviceHlp]

I think you only need to set variables declared as objects to nothing. This should be done in the reverse order of their setting (ie. the last object set should be the 1st object set to nothing). I'll see if I can find a link to some real feedback. Also, your 1st error handler should exit sub before the next error handler routine. Dave

 

[Firefly2012]

VBA has its own garbage collector so I'm not really sure you are doing something strictly worthwhile (although it's good practice for languages where this really matters). At worst it costs you the time to apply a few key strokes

 

[shg]

I think there are some corner cases where it's necessary when you've done something so complicated that VBA'a reference counting fails, but in general, it's unnecessary defensive programming that clutters your code. Variables of all types are destroyed when they go out of scope.

 

[20 Ton Squirrel]

I'm certain that there are situations that warrant such behavior, especially in cases where variable scope may exist outside the procedure. It's good to know that it isn't EXPLICITLY necessary, however. I'll be happy to unclutter my programming.

A fine discussion, thank you all for sharing!

Also, good catch NdNoviceHlp. Fortunately this was fluff I threw up here to visualize my madness.

 

[NdNoviceHlp]

Here's a link. See the part about objects. Dave
http://www.decisionmodels.com/memlimitsd.htm

 

[Firefly2012]

That looked more like support for not setting objects to Nothing because then you avoid the possibility of orphaning them by doing it in the wrong sequence!

 

[NdNoviceHlp]

Interesting. Maybe we could use a bit more learning if anyone else has a preference that they would like to share. I would prefer to ignore the whole business if it were possible. I'm guessing it's not nb until your wb gets large.... which makes it hard to prove/disprove. Dave

 

[shg]

I'm certain that there are situations that warrant such behavior, especially in cases where variable scope may exist outside the procedure.

When variables are more broadly scoped, it is frequently for the very reason that you want them to persist.

I'd never say never, but have personally never had occasion to believe it was necessary or appropriate.

 

[xenou]

Generally not necessary. If you look at my code you will see I do it anyway with most non-Excel objects, but not because I need to. I think the only exception I know of is DAO objects (and even that may be out of date). VBA is basically very good about "garbage collection" and, well, let's face it, memory is cheap and plentiful these days.