12

I've noticed that some members of the Stack Overflow community will use Set Object = Nothing in closing procedures. I was able to find why this is useful for instances of Access, but no answer has been satisfying when it comes to doing this for Excel, so my question is What are the benefits of setting objects to Nothing in VBA?

In the sample code below, is setting my objects ws and Test equal to Nothing a waste of space? Else, if doing so is in fact good practice, why?

Dim ws as Worksheet
Dim Test as Range

Set ws = Sheets(“Sheet1”)
Set Test = ws.Range(“A1”)

    'Utilize Test variable (copy, paste, etc)

Set Test = Nothing
Set ws = Nothing

Exit Sub
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • 1
    You release memory and avoid errors. Usually this is performed with objects that load into memory (userforms for example, dictionaries). Ranges, charts, workbooks, I haven't seen a problem that they don't go into "waste" after code execution. Thus, I don't see why to do it with these kind of variables. – Sgdva Jun 27 '18 at 14:42
  • 1
    In most cases you don't need to do it. Look for a discussion [here](https://blogs.msdn.microsoft.com/ericlippert/2004/04/28/when-are-you-required-to-set-objects-to-nothing/) or [here](https://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions) – Storax Jun 27 '18 at 14:55
  • Page not found @Storax. Sounds like the consensus is that I can just delete here :) – urdearboy Jun 27 '18 at 14:56
  • VBA has garbage collection so this should not be a problem.... However..... I have seen this cause problems when people forget to quit instances of things... they at times appear to keep running in the background sucking up memory. – QHarr Jun 27 '18 at 15:25
  • 5
    @QHarr VBA isn't garbage-collected, it's reference-counted. *In theory* the refcount gets depleted proper when the scope exits. In practice... it depends on who wrote the library for the object being cleared - if it's a VBA class or something in the host application (like a `Range`), don't bother. If it's a 3rd-party class from some reference type library, it can't hurt - but it's probably redundant. Also worth noting, if the object was declared with `As New {class-name}`, then setting it to `Nothing` has no effect whatsoever. – Mathieu Guindon Jun 27 '18 at 15:30
  • @MathieuGuindon Oh no......Thank you for correcting. It is a really useful comment. I hope it is all in the answer below as I will be bookmarking. – QHarr Jun 27 '18 at 15:57

1 Answers1

17

If this was managed .NET code (which is garbage-collected), you'd have to Release every single COM object you ever accessed, lest the host process (EXCEL.EXE) would likely remain running in the background, consuming memory and unable to completely tear down.

But this is VBA code (which is reference-counted), moreover VBA code that uses objects that the host application controls - these objects will die when the host application shuts down, and when that happens the VBA execution context is long gone.

In other words, all these Set ... = Nothing instructions are completely redundant.

In some specific cases, when you're dealing with a 3rd-party API / type library, it's possible that objects don't entirely clean up. For example you might be creating an Access.Application instance, and find that a "ghost" ACCESS.EXE process remains open in Task Manager well after Excel exited: that's a sign that you're leaking an object reference somehow, somewhere, and Set ... = Nothing can help prevent that.

However I wouldn't recommend systematically nulling all object references like that. Only when not doing it causes a problem. And even then, it's going to be one or two objects dragging everything down, not all of them. If ACCESS.EXE shuts down properly, there's no reason to clutter up your code with such instructions.

Avoiding storing object references in global state helps, too. If everything is local, in theory all objects involved are destroyed as soon as the local scope exits.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thank you for clearing this up. I kept seeing it and was beginning to wonder if I was making a newbie mistake by not doing so. All instances of **Set Nothing** have been removed! – urdearboy Jun 27 '18 at 15:44
  • So how does this fit with IE instances for example grinding a PC to a halt even though out of scope when coders haven't quit the application. Is this a distinction on a process over an object? – QHarr Jun 27 '18 at 16:01
  • 7
    @QHarr I don't do IE stuff in VBA, but if I did, I'd probably enclose the instance in a `With` block, e.g. `With New InternetExplorer` or whatever the class name is: when the `With` blocks owns the instance, the object dies when `End With` is reached. – Mathieu Guindon Jun 27 '18 at 16:03
  • Ok. Thank you . – QHarr Jun 27 '18 at 16:05
  • 2
    That's a really nice note on the `With` blocks – Kubie Jan 18 '19 at 19:45
  • @MathieuGuindon - you should take a look at [this question](https://stackoverflow.com/questions/73227756/variant-type-object-array-element-is-deallocated-by-with-statement) – urdearboy Aug 03 '22 at 21:23
  • 1
    @urdearboy interesting one! I've commented =) – Mathieu Guindon Aug 03 '22 at 22:02