object created in workbook_open event handler disappears on exit from the event handler 
Author Message
 object created in workbook_open event handler disappears on exit from the event handler

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



Fri, 05 Sep 2003 11:12:45 GMT  
 object created in workbook_open event handler disappears on exit from the event handler
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


Quote:
> 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



Fri, 05 Sep 2003 23:12:51 GMT  
 object created in workbook_open event handler disappears on exit from the event handler
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



Quote:
> 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



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

============================================================================
Quote:
> > ====
> > Distributed Systems Technology Centre (DSTC)
> > phone:-    +61 7 3864 1573
> > fax:-    +61 7 3864 1282
> > www:-    www.dstc.edu.au

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

- Show quoted text -

Quote:
> > ====
> > DSTC is W3C's Australian Office



Sat, 06 Sep 2003 07:41:02 GMT  
 object created in workbook_open event handler disappears on exit from the event handler
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



Sun, 07 Sep 2003 01:55:06 GMT  
 object created in workbook_open event handler disappears on exit from the event handler
Sorry Greg... I should have mentioned that the variable asObj is declared in
the general declarations section of the ThisWorkbook module...my oversight!

Toby

Quote:
> 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



Sun, 07 Sep 2003 11:57:12 GMT  
 object created in workbook_open event handler disappears on exit from the event handler
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



Mon, 08 Sep 2003 09:35:06 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. handling multiple events with one event handler

2. handling multiple events with a single event handler

3. create an add/modify/delete appointment event handler

4. Creating controls at runtime, assigning event handlers

5. sort event handler for dynamically created datagrid

6. Handler for object's events

7. How to create an doubleclick event handler for a column in a datagrid

8. Datagrid web control problem: creating event handlers for contols in template columns

9. Common event handler for objects

10. Common event handler for objects (2)

11. Event handler complexity, and private objects

12. Event handlers for controls created at runtime

 

 
Powered by phpBB® Forum Software