Class Module

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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,

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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. But maybe that's because I am on a train wending my way home with a glass (or to be accurate - the remains of a bottle) of wine after a rather nice steak.

Have fun. I always enjoy your contributions.

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