I am trying to create an object from a class module in another workbook.
I followed Chip Pearson's various explanations from this forum. This one
is from the beginning of this year:

===========
You can change the instancing property of the class to "Public Not
Creatable" which will allow you to declare a variable of that class
type, but not create an instance of the class. In the project that
contains the class, create a public function in a standard code module
that create a new instance of the class and returns a reference as its
result.

For example,
' in the project that contains Class1
Public Function CreateClass () As Class1
Set CreateClass = New Class1
End Function

' in the project that need a Class1 variable
Dim C1 As ProjName.Class1
Set C1 = ProjName.CreateClass()

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
===========

and it works! However....not completely as I envisaged:

The class is stored in an Addin. Amongst others it contains a property
(TransFileChanged) that stores a 'change' status of another workbook.
When a cell change takes places in this workbook, the class is activated
and the status is set to True. That works.
Unfortunately, the Addin does not seem to store the proper value when
the code finishes that sets the status property. Accessing the property
from within the Addin gives me an 'empty' value. My gut feeling says it
has something to do with creating more than one instance of the class an
accessing the wrong one, but have not been able to solve it.

Some code:

=========================
External workbook: Module
=========================
Option Explicit

Public clsTrans As vbprjCLAXCheckCBWAddIn.clsTrans

Public Sub UpdateTranslation()

'First make a reference to the proper Class Module in the Addin:
Set clsTrans = vbprjCLAXCheckCBWAddIn.CreateTransClass()
'Set the property:
clsTrans.TransFileChanged = True

Set clsTrans = Nothing

Exit Sub
=========================


=========================
Addin: General module
=========================
Public Function CreateTransClass() As clsTrans
Set CreateTransClass = New clsTrans
End Function
=========================

=========================
Addin: Class module called: clsTrans
=========================
Private m_FileChange As Boolean 'Stores file change status.

Public Property Get TransFileChanged() As Boolean
TransFileChanged = m_FileChange
End Property

Public Property Let TransFileChanged(p_bolChanged As Boolean)
m_FileChange = p_bolChanged
End Property

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

The class is used within the Addin to keep track of some basic
properties and methods related to the external workbook. That means that
within the Addin the class is instanced as well, although I am not using
the class as a collection.

As said, the code flows through the Let property. But if I try to access
the property from within the Addin, the property is empty/nothing.

I was wondering if there would be anyone interested to give me some
pointers?

Excel XP
Windows XP


Best regards,

Bennie Douma

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

BenD

I am by no means an expert on the subject, but

Set clsTrans = Nothing

doesn't this destroy the link to the class, thereby emptying all properties?
Just a guess, but try removing this line and see what you get.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"BenD" wrote in message
...
I am trying to create an object from a class module in another workbook.
I followed Chip Pearson's various explanations from this forum. This one
is from the beginning of this year:

===========
You can change the instancing property of the class to "Public Not
Creatable" which will allow you to declare a variable of that class
type, but not create an instance of the class. In the project that
contains the class, create a public function in a standard code module
that create a new instance of the class and returns a reference as its
result.

For example,
' in the project that contains Class1
Public Function CreateClass () As Class1
Set CreateClass = New Class1
End Function

' in the project that need a Class1 variable
Dim C1 As ProjName.Class1
Set C1 = ProjName.CreateClass()

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
===========

and it works! However....not completely as I envisaged:

The class is stored in an Addin. Amongst others it contains a property
(TransFileChanged) that stores a 'change' status of another workbook.
When a cell change takes places in this workbook, the class is activated
and the status is set to True. That works.
Unfortunately, the Addin does not seem to store the proper value when
the code finishes that sets the status property. Accessing the property
from within the Addin gives me an 'empty' value. My gut feeling says it
has something to do with creating more than one instance of the class an
accessing the wrong one, but have not been able to solve it.

Some code:

=========================
External workbook: Module
=========================
Option Explicit

Public clsTrans As vbprjCLAXCheckCBWAddIn.clsTrans

Public Sub UpdateTranslation()

'First make a reference to the proper Class Module in the Addin:
Set clsTrans = vbprjCLAXCheckCBWAddIn.CreateTransClass()
'Set the property:
clsTrans.TransFileChanged = True

Set clsTrans = Nothing

Exit Sub
=========================


=========================
Addin: General module
=========================
Public Function CreateTransClass() As clsTrans
Set CreateTransClass = New clsTrans
End Function
=========================

=========================
Addin: Class module called: clsTrans
=========================
Private m_FileChange As Boolean 'Stores file change status.

Public Property Get TransFileChanged() As Boolean
TransFileChanged = m_FileChange
End Property

Public Property Let TransFileChanged(p_bolChanged As Boolean)
m_FileChange = p_bolChanged
End Property

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

The class is used within the Addin to keep track of some basic
properties and methods related to the external workbook. That means that
within the Addin the class is instanced as well, although I am not using
the class as a collection.

As said, the code flows through the Let property. But if I try to access
the property from within the Addin, the property is empty/nothing.

I was wondering if there would be anyone interested to give me some
pointers?

Excel XP
Windows XP


Best regards,

Bennie Douma

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Dick,

That idea crossed my mind as well, deleting that part does not make a
difference. I left it in because the instance within the external
workbook should be released.

Best regards,

Bennie Douma

Excel XP / Windows XP Home Edition

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!