How does one share a class module among XLS files? I can share macros for
example by storing them in Personal.xls or by creating a VBAProject reference to
the file which contains the code. I presume there's a way to share the code in
a class module short of replicating the module it in every xls file?

I've tried putting the code in a class module contained within a Library.xls
file that's already referenced but when I try to use the class code I get an
error that the user-defined type is not defined. It works though if I replicate
the class module in every XLS file that wants to use it.

Plainly I'm missing something.

Thanks...

Bill

You can't directly share a class module between projects. You
can, however, have a public function in the workbook that
contains the class that returns as its result a new instance of
the class. For example, suppose WB1.xls has a project name of
MyProj and a class named CMyClass.

Then, set a reference from WB2.xls to WB1.xls (in VBA go to the
Tool menu, choose References, and check MyProj). Then in a code
module in WB1.xls, create a function

Public Function GetClass As CMyClass
Set GetClass = New CMyClasss
End Function

In WB2.xls, instantiate the class with code like

Public Sub AAA()
Dim C As MyProj.CMyClass
Set C = MyProj.GetClass
' more code
End Sub

Ensure that the Instancing Property of the Class is
PublicNotCreatable, not Private.

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



"Bill Martin" wrote in message
...
How does one share a class module among XLS files? I can share
macros for
example by storing them in Personal.xls or by creating a
VBAProject reference to
the file which contains the code. I presume there's a way to
share the code in
a class module short of replicating the module it in every xls
file?

I've tried putting the code in a class module contained within
a Library.xls
file that's already referenced but when I try to use the class
code I get an
error that the user-defined type is not defined. It works
though if I replicate
the class module in every XLS file that wants to use it.

Plainly I'm missing something.

Thanks...

Bill

That was not what I expected. Thanks for pointing me in this direction Chip.
I'll tinker with it a bit to make sure I fully understand your meaning, but it
sounds good to me.

Thanks.

Bill
------------------------------
Chip Pearson wrote:
You can't directly share a class module between projects. You
can, however, have a public function in the workbook that
contains the class that returns as its result a new instance of
the class. For example, suppose WB1.xls has a project name of
MyProj and a class named CMyClass.

Then, set a reference from WB2.xls to WB1.xls (in VBA go to the
Tool menu, choose References, and check MyProj). Then in a code
module in WB1.xls, create a function

Public Function GetClass As CMyClass
Set GetClass = New CMyClasss
End Function

In WB2.xls, instantiate the class with code like

Public Sub AAA()
Dim C As MyProj.CMyClass
Set C = MyProj.GetClass
' more code
End Sub

Ensure that the Instancing Property of the Class is
PublicNotCreatable, not Private.

It is perfectly possible to reference the class module

1. the class needs to be defined as publinotcreatable.
2. it is worth renaming the VBA section from the standard ie no
VBAProject but maybe VBAGlobalClasses
3. you need to reference the workbook containing the Classmodule fro
the workbook where you want to use it. this is done using th
tools/reference in VBA screens.
4. now you should be able to reference the class module directly suc
as dim x as vbaglobalclasses.myclass

hope this help

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50463

Tony,

This isn't completely accurate. Though your steps are correct,
you still won't be able to create an instance of the class from
the calling workbook. You need a procedure in the project
containing the class to instantiate the class.


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


"tony h"
wrote in message
...

It is perfectly possible to reference the class module

1. the class needs to be defined as publinotcreatable.
2. it is worth renaming the VBA section from the standard ie
not
VBAProject but maybe VBAGlobalClasses
3. you need to reference the workbook containing the
Classmodule from
the workbook where you want to use it. this is done using the
tools/reference in VBA screens.
4. now you should be able to reference the class module
directly such
as dim x as vbaglobalclasses.myclass

hope this helps


--
tony h
------------------------------------------------------------------------
tony h's Profile:
http://www.excelforum.com/member.php...o&userid=21074
View this thread:
http://www.excelforum.com/showthread...hreadid=504639

I think I would call that a clarification rather than an inaccuracy. Bu
maybe that's because I am on a train wending my way home with a glas
(or to be accurate - the remains of a bottle) of wine after a rathe
nice steak.

Have fun. I always enjoy your contributions

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50463

I managed to get to this late today, and it worked very nicely. Thanks for your
assistance.

Bill
----------------------------
Chip Pearson wrote:
You can't directly share a class module between projects. You
can, however, have a public function in the workbook that
contains the class that returns as its result a new instance of
the class. For example, suppose WB1.xls has a project name of
MyProj and a class named CMyClass.

Then, set a reference from WB2.xls to WB1.xls (in VBA go to the
Tool menu, choose References, and check MyProj). Then in a code
module in WB1.xls, create a function

Public Function GetClass As CMyClass
Set GetClass = New CMyClasss
End Function

In WB2.xls, instantiate the class with code like

Public Sub AAA()
Dim C As MyProj.CMyClass
Set C = MyProj.GetClass
' more code
End Sub

Ensure that the Instancing Property of the Class is
PublicNotCreatable, not Private.