VBA Class: How to destroy


VBA Class Destroy

It is good practice to destroy a class instance that is no longer needed.

By Set Logger = Nothing the object reference that is assigned to the variable is released.

Image showing to assign object reference

Image showing to declare a class

Now, the variable can be go out of scope safely.

The sub Class_Terminate

The sub Class_Terminate is a sub that will be called automatically every time an assigned object reference is released. Or, in other words, every time an instance of the class is destroyed.

Image showing to destroy object reference

The sub Class_Terminate is used typically for cleaning up, e.g. closing a database connection or sending content to a file. In VBA, it is less frequenctly used than its counterpart Class_Initialize, but, it may come in handy some times. Object oriented jargon will refer to this sub as the destructor.

The sub is Public and, consequently, part of the class interface in VBA. Despite it being part of the class interface in VBA, it is probably best to see the sub Class_Terminate as a pure event routine and never call it explictely. In other programming languages you will see that the routine will often not be part of the class interface and cannot be called explicitely.

In VBA, the sub Class_Terminate cannot have arguments.

If you would like to test, put a messagebox in it. Each time the sub is executed, the messagebox will appear and you will see each time the sub is executed.

Image showing a message box in Class_Terminate

Why is the sub Class_Terminate important?

Have a look at the alternative.

The alternative is that you create, in the class, a Public sub with the same code as in the sub Class_Terminate. Let's call this sub Terminate.

Image showing a public sub Initialize

Here is how, in this case, you would correctly destroy an instance of the class. Note the line Logger.Terminate.

This code works fine: you terminate what needs to be terminated, then the object reference is released and finally, the variable can go out of scope. However, your class documentation will need to be very explicit on the fact that, to guarantee that the class instance is destroyed correctly, the sub Terminate has to be called. At some point, somebody is bound to forget which, depending on the content of the sub Terminate, may be bad or not so bad.

The added value of sub Class_Terminate lies in the fact that it is called automatically. It cannot be forgotten. It cannot be bypassed. You, as a designer of the class, are sure that any class instance will always be destroyed correctly and the user of the class has one less thing to remember. The organization of the code is better, facilitating everybodies coding life.

To clean up, or not to clean up?

Personally, I take pleasure in cleaning up.

Even so, it is easy to forget. Don't worry though, because, if you forget, VBA will do the work for you. VBA has a so-called garbage collector that will do the dereferencing for you - if you forget. For those interested, the technique that the VBA garbage collector uses is called reference counting.

To explain why cleaning up is important, let's take a worst case scenario: you forget the line Set ... = Nothing and you are working in a programming language that has NO garbage collector (so not VBA). Such programming languages exist. Then, the class variable goes out of scope, but the memory taken by the object reference would never get released (= given back to the operating system). This is called memory leakage.

Image showing to assign object reference

Image showing memory leakage

If the memory is substantial, or, the memory leakage happens in a routine that is called many many times, this might affect the performance of your computer (or server). The only way - we know - to free up the memory is to restart.