title: What is the lifetime of a global variable in excel vba?
body: <p>I've got a workbook that declares a global variable that is intended to hold a COM object.</p>
  
  <pre><code>Global obj As Object
  </code></pre>
  
  <p>I initalize it in the Workbook_Open event like so:</p>
  
  <pre><code>Set obj = CreateObject("ComObject.ComObject");
  </code></pre>
  
  <p>I can see it's created and at that time I can make some COM calls to it.</p>
  
  <p>On my sheet I have a bunch of cells that look like:</p>
  
  <pre><code>=Module.CallToComObject(....)
  </code></pre>
  
  <p>Inside the Module I have a function</p>
  
  <pre><code>Function CallToComObject(...)
     If obj Is Nothing Then
          CallToComObject= 0
      Else
          Dim result As Double
          result = obj.GetCalculatedValue(...)
          CallToComObject= result
      End If
  End Function
  </code></pre>
  
  <p>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.</p>
  
  <p>Can someone explain what I should be looking for that can cause this?</p>