Closing C# object through excel code

Question

I have created a C# library's COM object in VBA code (Excel). This C# library logs all messages in the log file. I have written some code in finally method and wish to invoke it each time I close the object through excel. But whenever I close the object through excel it does not call the code available in finally method. Please suggest how should I forcibly invoke the finally code through excel.

Answer 1

Hello,
You actually want your "finally" code to go in the Class Destructor. Once this code is written, it will execute when there are no instances of the class left. VBA (thus, VB6) doesn't have a garbage collector per se, but it does use reference counting to automatically clean up after itself. When there are 0 references to an object in memory that object's destructor will be executed and the object destroyed.
How would this work? When you create an instance of the Object that is 1 reference: Set MyClass = New SomeClass when that variable is set back to nothing the reference count is decremented back to 0. This can happen two ways:

Manually: Set MyClass = Nothing

Or when the function/sub ends. The variable falls out of scope and thus the reference counter is decremented and the object is destroyed.

If your VBA Class Object is local in scope (declared in a procedure) then you really don't need to do anything to make sure the destructor runs, it will automatically execute when the procedure ends. If it is a module level variable in a VBA Class Module, then when the instance of the VBA class is destroyed all it's module level variables will be destroyed, and the destructor will run. If you have it as public/global variable in a standard module, then it will be destroyed when the host application is closed. Essentially it will behave like the Class_Terminate event in VBA.

Hope that helps!