Excel VBA Sharing class modules over a network

Posted on 2012-04-13
Last Modified: 2012-04-15

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

Question by:Murray Brown
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 45

Accepted Solution

aikimark earned 200 total points
ID: 37845540
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.
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37845647
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.


Author Comment

by:Murray Brown
ID: 37845842
LVL 24

Assisted Solution

Bitsqueezer earned 300 total points
ID: 37848091

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.



Author Closing Comment

by:Murray Brown
ID: 37848900
thanks very much

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question