[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 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