module-level variable lifetime

J

Jessie

This question looks like its been asked several hundred times, but a search
through Google/manuals still hasn't cleared this up for me:

What exactly is the lifetime of module-level variables (standard .bas
modules and thisworkbook module) ??

Heres some test code I put together:

* I create a helper Class Module, class1, that looks like this.

Private Sub Class_Initialize()
MsgBox "Hello world"
End Sub
Private Sub Class_Terminate()
MsgBox "Goodbye"
End Sub

* Now in either a new .BAS module or the workbook module, I create a global
variable.

Public x as class1

* I need to instantiate the variable somehow, so I add the following to the
workbook module.

Private Sub Workbook_Open()
Set x = New Class1
End Sub


NOW,

If I open the workbook, the msgbox tells me that the class has been created.
GOOD.
But when does the class ever get destroyed?
If I close the workbook, I dont get a msgbox.
If I shutdown the application, I dont get a msgbox (although I assume the
Class must be destroyed at this point at least)

The only way I can get the class to Terminate is to set the variable =
Nothing, in the Workbook_BeforeClose event...

So what is going on? Why aren't module variables being released when
workbooks are closed?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top