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...