What are the benefits of setting objects to “Nothing”

by

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.

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. VBA comma separated cells, needt last entry only
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. Open Excel file for reading with VBA without display
  7. Wait for shell command to complete [duplicate]
  8. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  9. How to add a named sheet at the end of all Excel sheets?
  10. How to remove all non alphanumeric characters from a string except period and space in excel?
  11. Find last used cell in Excel VBA
  12. VBA general way for pulling data out of SAP [closed]
  13. Does Dir() make any guarantee on the order of files returned?
  14. Call a Sub with a String
  15. Assign on-click VBA function to a dynamically created button on Excel Userform
  16. Delete Row based on Search Key VBA
  17. How to add a button programmatically in VBA next to some sheet cell data?
  18. Assign keyboard shortcut to run procedure
  19. return csv file as recordset
  20. Excel macro -Split comma separated entries to new rows [duplicate]
  21. Get text from clipboard using GetText – avoid error on empty clipboard
  22. Copy a selected range to another worksheet
  23. Benchmarking VBA Code
  24. Excel macro – paste only non empty cells from one sheet to another
  25. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  26. Display milliseconds in Excel
  27. VBA editor auto-deletes spaces at the ends of lines
  28. Application.Quit command not closing the entire Excel Application
  29. Trying to open the workbook in separate instance
  30. How to protect Excel workbook using VBA?

Leave a Comment