How to use Terminate event in VBA?

In VBA (Visual Basic for Applications), the Terminate event is used in class modules to execute code just before an instance of a class is destroyed. This event can be useful for cleaning up resources, such as closing files or releasing memory that the object may have been using during its lifetime. To use the Terminate event in VBA, you’ll first need to have a class module where you want to define some cleanup code to run when an object instance of that class is about to be terminated.

Here are the steps to use the Terminate event in a class module

1. Open the VBA editor by pressing Alt + F11 in your Office application (e.g., Excel).
2. Insert a new class module by right-clicking in the Project Explorer window, selecting Insert, then Class Module.
3. In the Properties window (usually located below the Project Explorer), give your class module a meaningful name.
4. Within the class module, define the Terminate event handler by typing the following code:

VBA


Private Sub Class_Terminate()
    ' Your cleanup code goes here
    MsgBox "The class instance is terminating."
End Sub

Replace the MsgBox line with any cleanup operations you need to perform. Here’s a simple example illustrating how you might use a class with a Terminate event. In this example, we’ll have a class that represents a temporary file that should be deleted when the class is terminated. Class module TemporaryFile:

VBA


Option Explicit

Private filePath As String

' Class Initialize event runs when an instance is created
Private Sub Class_Initialize()
    ' Initialize your temporary file here
    filePath = "C:tempmyfile.tmp"
    ' Create the temporary file or open the existing one
End Sub

' Class Terminate event runs when an instance is destroyed
Private Sub Class_Terminate()
    ' Cleanup code to delete the temporary file
    If Len(Dir(filePath)) > 0 Then
        Kill filePath
        MsgBox "Temporary file has been deleted."
    End If
End Sub

Now, to use this class in a regular module:

VBA


Sub UseTemporaryFile()
    Dim tempFile As TemporaryFile
    Set tempFile = New TemporaryFile
    
    ' Perform operations with the temporary file
    
    ' Once the object goes out of scope or is set to Nothing, the Terminate event is triggered
    Set tempFile = Nothing
End Sub

As soon as tempFile goes out of scope (which happens when the procedure UseTemporaryFile finishes) or is explicitly set to Nothing, the Terminate event is fired, and the cleanup code in Class_Terminate runs. Keep in mind that the Terminate event will only be called if the object was properly set to Nothing. If you end your VBA process abruptly (e.g., by pressing Stop in the VBA editor), the Terminate event might not run. To ensure resources are freed up properly, it’s good practice to explicitly set objects to Nothing when you are done with them.

facebooklinkedin