What is the lifetime of a global variable in excel vba?

Question

I've got a workbook that declares a global variable that is intended to hold a COM object.

Global obj As Object

I initalize it in the Workbook_Open event like so:

Set obj = CreateObject("ComObject.ComObject");

I can see it's created and at that time I can make some COM calls to it.

On my sheet I have a bunch of cells that look like:

=Module.CallToComObject(....)

Inside the Module I have a function

Function CallToComObject(...)
   If obj Is Nothing Then
        CallToComObject= 0
    Else
        Dim result As Double
        result = obj.GetCalculatedValue(...)
        CallToComObject= result
    End If
End Function

I can see these work for a bit, but after a few sheet refreshes the obj object is no longer initialized, ie it is set to Nothing.

Can someone explain what I should be looking for that can cause this?

Answer 1

Any of these will reset global variables:

1. Using "End"
2. An unhandled runtime error
3. Editing code
4. Closing the workbook containing the VB project

That's not necessarily an exhaustive list though...

Answer 2

I would suggest a 5th point in addition to Tim's 4 above: Stepping through code (debugging) and stopping before the end is reached. Possibly this could replace point number 3, as editing code don't seem to cause global variable to lose their values.