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
Improve this question
6
  • 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. Commented Jun 27, 2018 at 14:42
  • 1
    In most cases you don't need to do it. Look for a discussion here or here Commented Jun 27, 2018 at 14:55
  • Page not found @Storax. Sounds like the consensus is that I can just delete here :) Commented Jun 27, 2018 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. Commented Jun 27, 2018 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. Commented Jun 27, 2018 at 15:30

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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!
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 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.
That's a really nice note on the With blocks
@urdearboy interesting one! I've commented =)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.