When should an Excel VBA variable be killed or set to Nothing?

by

VB6/VBA uses deterministic approach to destoying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.

Object variables are guaranteed to be cleaned (set to Nothing) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required.

There are only two cases when you want an explicit cleanup:

  1. When you want an object to be destroyed before its variable goes out of scope (e.g., your procedure is going to take long time to execute, and the object holds a resource, so you want to destroy the object as soon as possible to release the resource).

  2. When you have a circular reference between two or more objects.

    If objectA stores a references to objectB, and objectB stores a reference to objectA, the two objects will never get destroyed unless you brake the chain by explicitly setting objectA.ReferenceToB = Nothing or objectB.ReferenceToA = Nothing.

The code snippet you show is wrong. No manual cleanup is required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code.

If you have a variable at a class level, it will be cleaned/destroyed when the class instance is destructed. You can destroy it earlier if you want (see item 1.).

If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item 1.).

Access level of a variable (public vs. private) does not affect its life time.

More Related Contents:

  1. How to run a string as a command in VBA
  2. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  3. How do I get values from a hidden worksheet using VBA [closed]
  4. How to copy cells that contain certain text to another worksheet [closed]
  5. VBA Macro for copying and pasting columns on same excel sheet
  6. Levenshtein Distance in VBA [closed]
  7. Run Excel Macro from Outside Excel Using VBScript From Command Line
  8. Excel VBA – Delete empty rows
  9. VBA Shell function in Office 2011 for Mac
  10. How to copy sheets to another workbook using vba?
  11. Check if value exists in column in VBA
  12. hiding formulas in formula bar
  13. How to use Events with Option Button Controls on Userform [duplicate]
  14. Writing an array to a range. Only getting first value of array
  15. How do I use variables to set properties in VBA (Excel)
  16. VBA: Else without If Error
  17. VBA Many buttons point to the same _Click sub
  18. Convert RTF (Rich Text Format) code into plain text in Excel
  19. VBA paste as values – how to
  20. What are the benefits of using Classes in VBA? [closed]
  21. Writing a VLOOKUP function in vba
  22. Open a workbook from VBA and disable Workbook_Open() code?
  23. Handle cancellation of InputBox to select range
  24. How to filter listbox values based on a Textbox value
  25. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  26. Convert image (jpg) to base64 in Excel VBA?
  27. VBA: How to combine two ranges on different sheets into one, to loop through
  28. Finding out reason of Pyomo model infeasibility
  29. Excel VBA – How to Redim a 2D array?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment