Programming Excel with VBA and .NET

Destroy Objects

In Visual Basic, objects remain in memory as long as there is a reference to them in scope. What’s that mean? Scope is determined by where the variable is declared. So, a Message declared within a procedure is in scope while that procedure executes. When the procedure ends, the Message goes out of scope and it is removed from memory (some people say it gets destroyed).

At that point, the object is no longer available and any property settings it contained are lost. A way to prevent that is to make a reference at another level of scope. For instance, the following m_Messages variable keeps the Message collection around after TestInitializeCollection ends:

    ' TestMessage module
    Public m_Messages As Messages

    Sub TestInitializeCollection( )
        ' Intialize the Messages collection.
        Set m_Messages = New Messages
        ' Create some messages
        Dim msg1 As New Message
        msg1.Title = "Msg1"
        msg1.Value = "From collection."
        msg1.icon = Information
        m_Messages.Add msg1
        ' and so on...
    End Sub

The trick here is that the msg1 object is also preserved, even though it is declared within the procedure that just ended. In this case, the collection holds a reference to that Message object, which keeps it in memory until the workbook closes or the object is explicitly destroyed. There are several ways to explicitly destroy the Message object:

• Remove it from the collection.

• Set the collection to Nothing.

• Set the m_Messages variable to a new Messages collection.

This code illustrates each technique: ...