Module-Level Collection Object (Scope Issues)

I am having some issues with an ActiveX DLL I've written in Visual Basic 6.  The DLL is being used as an automation add-in in Excel XP to provide worksheet functions that query an Access XP mdb.  

I am maintaining a collection of snapshot recordsets in a module-level collection in a class module named "clsSumRecs".  The worksheet functions are maintained in a different class module named "root".  

Here's an overview of the two class modules:

root
-----
Private csr As New clsSumRecs

Public Function Balance(Account As String, BalDate As Date) As Variant
    Balance = csr.GetBal(Account, BalDate)
End Function

Public Sub ResetData()
    csr.RequeryColls
End Sub


clsSumRecs
--------------
Private BalanceColl As Collection

Public Function GetBal(AcctNum As String, BalDate As Date) As Variant
    '1. Finds the recordset it needs by BalDate from the BalanceColl collection
    '2. Creates a new recordset and adds it to the BalanceColl collection if it can't find an existing recordset for BalDate
    '3. Returns the balance of AcctNum
End Function

Public Sub RequeryColls()
    '1. Checks to see if the BalanceColl collection exists
    '2. If BalanceColl exists, then it loops through the collection requerying each recordset
End Sub


The problem is that when I call RequeryColls from the ResetData in the root module, the collection object is empty.  However, if I call RequeryColls from the GetBal function in the clsSumRecs module, the collection is not empty and the recordsets get requeried as they should.  Why???

It is almost as if the GetBal function is passing the BalanceColl collection to the RequeryColls procedure, even though BalanceColl is a module-level variable common to both procedures.  Any ideas?

-mikeStart Free Trial