Excel VBA Sharing class modules over a network


In Excel VBA can classes be shared over a network by different users?
How would this work?

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could save the .cls files in a shared directory on the network and everyone could import them into their VBA environment.

You could also package these as an add-in.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris BottomleySoftware Quality Lead EngineerCommented:
I do not believe they can be truly shared even on a single pc since the VBA project is always unique to the instance of eat application.  Therefore the only way is export from the master system and import to EACH application in EVERY user system.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:

that's not correct. Of course you can share classes (classes, not the instantiated objects of the classes) with other Excel workbooks, if on the same computer or over the network doesn't make a difference as this is only the file location.

What you need to do:

1. Create an Excel macro-enabled workbook where you develop your class modules. Here it is important to use the second property of a class module which is usually set to "1-Private", set it to "2-PublicNotCreatable". This let external workbooks "see" the class module so they can declare an object variable with the type of this object (= the name of the class module). For example, if your class was named "clsMyClass" you can now use

Dim objMyClass As clsMyClass

Open in new window

in any external workbook which should use this class.

Unfortunately it is not possible to create the object like you usually do like this:

Set objMyClass = New clsMyClass

Open in new window

in the external workbook - that is what "Not Creatable" means. So how do you get the instance of the object? You need to add a standard module in your class library which supports the external object with instances like this:

Public Sub GetMyClass() As clsMyClass
   Set GetMyClass = New clsMyClass
End Sub

Open in new window

Now the external workbook can instantiate a new object on this way:

Set objMyClass = GetMyClass()

Open in new window

So you must create one helper sub in the standard module of your class library for each object you want to instantiate externally.

2. As all Excel workbooks normally are named "VBA Project" in the VBA editor you need to change this now to an individual name not used in any external workbook where you want to use your class library, otherwise you get an error because of a name conflict. So go to the VBA editor in your class library and right click on the "VBA Project" and use "Properties". Here you can give your project a unique name, for example "MyClassLibrary". Now save your class library and close it.

3. What you need to do in the external workbook is: Go to the VBA references and click on "Browse". Normally you use that to add DLLs which are not listed in the reference list. But you can see in the file type drop down that Excel also supports to add other workbooks as reference (which is also the case in other VBA versions like Word, Access and so on, all with their file type). That's all you need to share your class library: You can now search for the place where you saved your class library and add the xlsm file as reference to your current workbook. You will also see a new "folder" in your project which is called "References".

Now you can declare objects with the class names of your class library and you can use the helper subs to instantiate them.

Keep in mind that this method has the same problems like with other DLLs: If you ever change things in your class library it is changed for ALL workbooks which are using it. So if you delete a class or change it's name or parameters you maybe get in trouble with existing functionality of the external workbooks.
The best to avoid this is:
If you need to add parameters to existing classes which are already used in other projects, add them as optional parameters with a default value, this makes sure that you can use the class method like before, but also on the new way. Of course you need to test that the old way works like before (without the additional parameters).

If you don't need a class in your current external workbook or need a new name for it, let the old one unchanged and create a new class with the new name. This makes sure that all older projects can work with it like before and your new one uses the new version.


Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.