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

Question

I ve been teaching myself Excel VBA over the last two years  and I have the idea that it is sometimes appropriate to dispose of variables at the end of a code segment  For example  I ve seen it done in this bit adapted from Ron de Bruin s code for transferring Excel to HTML   Function SaveContentToHTML  Rng as Range           Dim FileForHTMLStorage As Object         Dim TextStreamOfHTML As Object         Dim TemporaryFileLocation As String         Dim TemporaryWorkbook As Workbook               TemporaryWorkbook Close savechanges  False         Kill TemporaryFileLocation         Set TextStreamOfHTML   Nothing         Set FileForHTMLStorage   Nothing         Set TemporaryWorkbook   Nothing  End Function   I ve done some searching on this and found very little beyond how to do it  and in one forum post a statement that no local variables need to be cleared  since they cease to exist at End Sub  I m guessing  based on the code above  that may not be true at End Function  or in other circumstances I haven t encountered   So my question boils down to this     Is there somewhere on the web that explains the when and why for variable cleanup  and I just have not found it    And if not can someone here please explain      When is variable cleanup for Excel VBA necessary and when it is not   And more specifically    Are there specific variable uses  public variables  Function-defined variables   that remain loaded in memory for longer than subs do  and therefor could cause trouble if I don t clean up after myself

User · Answer

I have at least one situation where the data is not automatically cleaned up, which would eventually lead to "Out of Memory" errors. In a UserForm I had:

Public mainPicture As StdPicture
...
mainPicture = LoadPicture(PAGE_FILE)

When UserForm was destroyed (after Unload Me) the memory allocated for the data loaded in the mainPicture was not being de-allocated. I had to add an explicit

mainPicture = Nothing

in the terminate event.

User · Answer

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   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    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

User · Answer

VBA uses a garbage collector which is implemented by reference counting   There can be multiple references to a given object  for example  Dim aw   ActiveWorkbook creates a new reference to Active Workbook   so the garbage collector only cleans up an object when it is clear that there are no other references   Setting to Nothing is an explicit way of decrementing the reference count   The count is implicitly decremented when you exit scope   Strictly speaking  in modern Excel versions  2010   setting to Nothing isn t necessary  but there were issues with older versions of Excel  for which the workaround was to explicitly set

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

Examples related to variables

Examples related to excel

Examples related to vba