Situation:

In my Excel AddIn I am trying to create an object from a DLL (also mine). If
I create the object in the workbook_open event for my AddIn
ie:  set ref = createobject("myClass")

The object is created as expected (BTW: ref is a private variable, of the
correct type, contained within ThisWorkbook of my AddIn) but when the
workbook_open event handler (also located in ThisWorkbook of my AddIn) my
ref variable is reset to nothing & my created object is no more.

I need this object to continue to be available in ThisWorkbook until I set
the ref back to nothing.

Does anybody know why this is happening?

Regards

Toby Chittenden

============================================================================
====
Distributed Systems Technology Centre (DSTC)
phone:-    +61 7 3864 1573
fax:-    +61 7 3864 1282
www:-    www.dstc.edu.au
============================================================================
====
DSTC is W3C's Australian Office

Toby:

You will need to post your code in order for anyone to even guess what is going
on. I'm sure the problem will reveal itself upon analyzing the code - both the
code that creates the object and the "workbook_open" event procedure.
--
Bruce M. Thompson

Apologies Bruce et al.

My workbook_open code is:

----------------------------------------------------------------------------
--------------------------------
Private Sub Workbook_Open()
'event handler: intercepts open event for ThisWorkbook
'calls initialise to initialise the ActiveSheets system

   On Error GoTo errorHandler

3: Call main.argTypesInit                 'initialise argument types for use
4: asState = False                              'initialise connection state
flag

5: Call openAuxFile(Me.Path, AS_AUX)      'open the ActiveSheetsStore
workbook

   'set a reference from this AddIn to the ActiveSheetsAux file
6: Call ThisWorkbook.VBProject.References.AddFromFile(Me.Path & "\" &
AS_AUX)

7: Set asObj = CreateObject("ActiveSheets.CActiveSheetsEngine")
9: Call stopActiveSheets                    'initialise ActiveSheets to
disabled state
   Exit Sub

errorHandler:
   MsgBox "Workbook_Open Error," & Erl & "," & Err.Number & "," _
      & Err.Description & "," & Err.Source
   Call Err.Clear
   Resume Next
End Sub
----------------------------------------------------------------------------
-----------------------------------

Lines 3/4 simply initialise some values required by my App.
Line 5 opens a hidden workbook that is used by my App & line 6 sets a
reference to that workbook.

Line 7 is where my object is created (& is created successfully, so I know
that the object initialisation works OK) - however, when this sub exits, the
'asObj' reference is reset to 'Nothing' & I cannot access my created object.

I have tried creating my object later in the initialisation process (ie:
after the workbook_open sub is run) & again the object is successfully
created, but this time the object remains accessible until I explicitly set
the 'asObj' reference to 'Nothing'. This is OK except that I need some of
the objects features to be accessible earlier - namely as soon as the
workbook_open event is fired.

Line 9 sets my App to a known state prior to use by the End-User (I have
checked that there is nothing within the 'stopActiveSheets' sub that 'blows
away' my object.

Does this help?

Toby Chittenden

============================================================================
============================================================================

Hello Toby,

If I understand correctly, this is the line in question:

7: Set asObj = CreateObject("ActiveSheets.CActiveSheetsEngine")

Do you have a Dim statement for the asObj variable?

If yes, you should move the Dim statement to the very top of the
ThisWorkbook module (outside of all procedures). This is the General
Declarations section of the module. The syntax would be:

Dim asObj as Object
'or
'Private asObj as Object

When you declare variables in the General Declarations section of the
ThisWorkbook module, they maintain their lifetime while the workbook is
open.  This is different than not declaring the variable, or declaring the
variable in the actual procedure. Then the variable lifetime is only while
that procedure runs.

HTH,
 Greg Ellison
 Microsoft Developer Support

Sorry Greg... I should have mentioned that the variable asObj is declared in
the general declarations section of the ThisWorkbook module...my oversight!

Toby

Hello Toby,

I believe I know the problem. I can reproduce the problem using this code:

Contents of ThisWorkbook Module:
------------------------
Option Explicit

Private obj As Object

Private Sub Workbook_Open()
    ThisWorkbook.VBProject.References.AddFromGuid
"{00020905-0000-0000-C000-000000000046}", 0, 0
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    TestObj
End Sub

Public Sub TestObj()
    MsgBox "TypeName of obj: " & TypeName(obj)
End Sub
------------------------

I open the workbook and the Open event automates Word and calls TestObj to
display the typename of the variable, which is "Application".  But, when I
run the Thisworkbook.TestObj procedure again, it displays "Nothing."

The problem is caused by adding a reference to the VBA project
programmatically.  If you remove that line, the problem does not occur.  
But by adding a reference, this forces the VBA project to "reset" itself.
The same happens if you programmatically add a procedure to the project.
The project resets because it needs to recompile.  This causes any public
variables to reset and is considered by design.

I'm not sure of the best way to code around this. One idea is to use a
ontime macro. For example, I could replace the above scenario with the
following and it works around the problem:

Option Explicit

Private obj As Object

Private Sub Workbook_Open()
    ThisWorkbook.VBProject.References.AddFromGuid
"{00020905-0000-0000-C000-000000000046}", 0, 0
    'Now, end this sub and enable a new sub to run the remainder of the
code:
    Application.OnTime Now + TimeValue("00:00:01"), _
        ThisWorkbook.Name & "!ThisWorkbook.Workbook_OpenContinue"
End Sub

Private Sub Workbook_OpenContinue()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    TestObj
End Sub

Public Sub TestObj()
    MsgBox "TypeName of obj: " & TypeName(obj)
End Sub

HTH,
 Greg Ellison
 Microsoft Developer Support