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?

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

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

Comments

[Tim Williams]

Do you have multiple function calling your COM object, or just the one? If only one then you might consider making it a Static variable within the function: the function can then check it's initialized, and if not, initialize it.

[Jean-François Corbett]

Aren't global variables evil? Also, I believe the right keyword is Public, not Global.

[Jon49]

Where could one find an exhaustive list. I've always wondered what has stopped my global variables from working. Especially the variable to catch app events.

[Joseph Shih]

@Tim, I'm guessing it's the unhanded runtime error that's causing it

[Tim Williams]

@Jon: those were all I could think of.

[Jon49]

What did you mean by Using "End"? Can you end a global variable? I'm familiar with End With, End Sub, End If, etc. - Thanks for the info, very valuable. I knew about 2 and 4 but didn't know about 1 and 3.

[Tim Williams]

@Jon: End by itself - "Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables."

[Sandra Rossi]

It would be nice to enrich the answer by adding @ExcelDevelopers "manual stop during debug". The 3rd point would better be "editing code such a way that it leads to a project recompilation" (examples: it recompiles after adding procedure, changing signature of procedure, etc.; it doesn't recompile after adding module or adding property, etc.) Reset concerns also Static variables (part of global memory). The reset of the global memory of one VBProject doesn't reset the global memory of other VBProjects. Question & answer are about Excel, they could be generalized to all VBA-based applications.