Make VBA Class Module Library

Hi, I have a bunch of class modules that I have made in Excel vba. A bunch
of my Excel programs use them and I was wondering if there is a way to make
them into a library of some sort so that I can have one version of all of
them that each project can link to so I can make a change in one place and
not in each of the projects. Thanks.

Mark

You can do this, but you've got to jump through a few hoops. Put
all your class modules in one project, and change the name of
that project from the default VBAProject to something unique,
like MyProj. (To do this, in VBA go to the Tools menu, choose VBA
Project Properties, and then change the name.)

Then, ensure that each class module's Instancing property is 2 -
PublicNotCreatable, not 1 - Private. (To do this, open the class
module and press F4 to get the properties window. There, change
the Instancing property.)

The projects that will be using these class modules will be able
to declare a variable with a type of your class, but will not be
able to create a new instance of the class, using the New
keyword. Therefore, in the MyProj project, create a function for
each class module which creates a new instance of the class and
returns it as its return value. For example, if you class is
named MyClass1, your procedure would be

Public Function GetMyClass1() As MyClass
Set GetMyClass1 = New MyClass
End Function

Write a procedure like the above for each class module.

Now, in the project that will be using the class, set a reference
to MyProj (in VBA, go to the Tools menu, choose References, and
select MyProj from the list). Once you've established your
reference, you can write code like


Public C As MyProj.MyClass1
Set C = MyProj.GetMyClass1()


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






"Mark Olsen" <[email protected]> wrote in
message
news:[email protected]...
> Hi, I have a bunch of class modules that I have made in Excel
> vba. A bunch
> of my Excel programs use them and I was wondering if there is a
> way to make
> them into a library of some sort so that I can have one version
> of all of
> them that each project can link to so I can make a change in
> one place and
> not in each of the projects. Thanks.
>
> Mark