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

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

ID: 37848900
thanks very much

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now