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
' 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
Removeit from the collection.
Set the collection to
m_Messagesvariable to a new
This code illustrates each technique: ...