none
vba and reference counting RRS feed

  • Question

  • Hello Excel Devs!

    I couldn't find answers around the internet, so I decided to ask here.

    My Excel application is manipulating data in external data sources - mostly MS SQL server, but also Oracle, Pervasive databases etc. I developed an engine with consists of some code modules and two class modules. It's working fine, however I encountered rather strange behaviour that doesn't let me sleep.

    I declare a class named GlobalConnection in a global scope and I'd like it to stay alive through the lifetime of a Workbook. Thus, I decided to implement a controlled circular reference to fool reference counting:

    Code module Global scope:

    option explicit public GlobalConnection as MyClass

    Class module MyClass:

    option explicit

    ' some ado objects, methodes, properties and:

    public Fool_ReferenceCounter as ClassFoolRC

    Class module ClassFoolRC:

    option explicit
    
    public foo as MyClass
    


    Workbook_open event calls a procedure that sets GlobalConnection and thus fires class_initalize event.

    ' Procedure fired by Workbook_open event:
    
    sub InitializeApplication()
    
        set GlobalConnection = new MyClass 
    
    end sub
    
    ' MyClass class_initialize event:
    
    sub class_initialize()
    
        ' init class stuff etc.
    
        ' and in the end:
    
        set Fool_ReferenceCounter = new ClassFoolRC
    
        set Fool_ReferenceCounter.foo = Me
        
    
    end sub

    This is producing a circular reference to avoid reference count drop to 0.

    Why am I doing this? I was trying to fool reference counting because of a very strange VBA behaviour:

    when Excel goes to idle something strange happens. After some time (few minutes) MyClass is being automaticly destroyed by Excel. When I try to refeer to it after given time, I get compiler error saying that object is set to nothing. Even with circular reference set to another object.

    What am I doing wrong?

    I would like MyClass to stay alive through the lifetime of workbook, because it holds opened connection to a datasource. Having global (or public) instance of MyClass is very comfy, cause engine opens and closes connection to data source only once (events workbook_open and workbook_beforeclose). My workbook does very frequent data inputs and refreshings, so working on opened connection greatly rises it's speed and efficency.

    Here is a little piece of code I use to count references:

    Function HowManyReferences(oObject As IUnknown) As Long
        
        On Error GoTo ErrorHandling
        
        If (oObject Is Nothing) = False Then
            
            CopyMemory HowManyReferences, ByVal (ObjPtr(oObject)) + 4, 4
            HowManyReferences = HowManyReferences - 2
        
        Else
            
            HowManyReferences = 0
        
        End If
        
        If Err.LastDllError Then
            
            HowManyReferences = -1
            Err.Clear
        
        End If
        
        Exit Function
    
    ErrorHandling:
        
        Debug.Print "Could not count references: " & Err.Description
        IloscReferencji = -1
    
    End Function

    I was quadruple checking I am pretty sure that circular reference is set corectly. Class_Terminate event isn't fireing.

    If so, why is MyClass auto-killed after few minutes of idle time? Could it be that database has something like "close unused connection timeout" with affects ADODB.objects in MyClass? That would maybe shut connection but could not set MyClass to nothing.

    Any ideas/directions would be greatly appriciated!

    Friday, July 19, 2013 8:41 AM