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

18
votes

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?

2
vbaexcelglobal-variables
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. – Tim Williams
Aren't global variables evil? Also, I believe the right keyword is Public, not Global. – Jean-François Corbett

2 Answers

22
votes

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

9
votes

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.