Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel VBA Sharing class modules over a network

Posted on 2012-04-13
Medium Priority
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 46

Accepted Solution

aikimark earned 800 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 1200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

618 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