18

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?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
chollida
  • 7,834
  • 11
  • 55
  • 85
  • 1
    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 Aug 12 '11 at 18:11
  • 1
    Aren't global variables evil? Also, I believe the right keyword is `Public`, not `Global`. – Jean-François Corbett Aug 15 '11 at 08:17

2 Answers2

23

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

chollida
  • 7,834
  • 11
  • 55
  • 85
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 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. – Jon49 Aug 12 '11 at 19:11
  • @Tim, I'm guessing it's the unhanded runtime error that's causing it – chollida Aug 12 '11 at 19:19
  • @Jon: those were all I could think of. – Tim Williams Aug 12 '11 at 19:20
  • 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. – Jon49 Aug 12 '11 at 19:40
  • 4
    @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." – Tim Williams Aug 12 '11 at 19:46
  • 1
    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. – Sandra Rossi Sep 27 '20 at 09:13
10

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.

Excel Developers
  • 2,785
  • 2
  • 21
  • 35