Setting up a common subroutine(s) module/reference in Excel VBA

Copper Contributor

I need to have a common set of subroutines that can be used in .xlsm files while assuring subroutine consistency from one target file to another.   I have done this successfully just very recently.  However, now it doesn't seem to work.

I wrote a procedure for this:

Create the COMMON file:
1. Close any Excel files that are open.
2. Create a blank workbook and give it a name like COMMON .. Something.
3. Open VBA
4. Create a Module
5. Paste in any subroutines or functions
6. Select the Project in VBA Project Explorer.
7. At the bottom of the Project Explorer, should be a Properties section.
8. Change the Name in the Properties section to something like COM_SUBS_XYZ
9. Save the file as .XLSM !!
Reference the COMMON file in a target workbook:
1. Close Excel or at least all the files that are open.
2. Open the COMMON file.
3. Open the target file.
4. Open VBA
5. In the VBA Project Explorer, highlight the target Project.
6. In VBA Tools/References, check the box next to the COM_SUBS_XYZ entry.
7. Now, the target Project should show the name of the Common .xlsm file in a References section.
The connection will now be made and both workbooks will open when the target workbook is opened.

Unfortunately, the expected item isn't shown in the Tools/References list.  Or, if it is, the title is VBA Project.  If I check box that one, I get an error that says the name is already used.

How to fix?

6 Replies


I'd either save the code in your personal macro workbook PERSONAL.XLSB, or in a workbook that you save as an Excel Add-in (*.xlam). If you choose the latter, activate the add-in in File > Options > Add-ins.

Yes, I had considered the various options and decided that this would work best in our situation. Of course, that doesn't mean I couldn't revisit that idea and try again.
Unfortunately, the expected item isn't shown in the Tools/References list.

why you need them shown in Tools/Reference.
Only com active class items with Registry entries can be showed here.
Vba xlam or xlsm are not com class so they are not showed.
pieyezhu: Well, I can see that you know more about the mechanics of this than I. However, this process was recommended and *had* been working. How do we reconcile the difference?
Re:How do we reconcile the difference?
Sorry,I am not good at English?
What do you mean reconcile the difference?
The difference is:
It had been working for me as had been recommended.
Then it stopped.
You have suggested that it could not have worked in the first place.
So, we differ and I don't know how to resolve that.... so I asked.