Sharing VBA Modules across Workbooks

Question

I have a number of workbooks. Each workbook contains a number of spreadsheets, and all workbooks are exactly the same, except for the data they carry. Each workbook has a number of VBA modules, and when I make changes to one module, I then need to push the same changes to all other modules to all other workbooks.

Is there a way to share the VBA modules across all of my Workbooks, so I can make changes only in one place?

Answer 1

Move the code into a separate Excel workbook and save it as .xlam (Excel macro-enabled add-in).

Reference the add-in from all the other files' Tools - References (click Browse, change the file type to "Microsoft Excel files" and navigate to the xlam file).

Go through the code and make sure it is written correctly and properly references workbooks and worksheets. E.g. ThisWorkbook will now refer to the add-in workbook, not to the document calling it.

Answer 2

I got an amateur fix for this kind of stuff. At my office, there are like 15 persons who share some utilities and UDFS on EXCEL. We had to change 15 Excel Workbooks when the code had to be revised or improved. Waste of time

First of all, I must say that for this solution to work, all users needs to share a folder where you can put a shared workbook for everyone to access. If users cannot share anything, then this solution won't work.

We found an easy solution for us. I'm pretty sure there is, indeed, a better way to do this, and more elegant, but this works for us for the last 10 years and we've had enough.

We created a MASTER WORKBOOK with all subs and functions we needed. I'll make a quick example. This master workbook is named MASTER_WB (title of VBAProject is also MASTER_WB)

This MASTER_WB would hold all your utilities and common codes. As you can see in the image, I typed 1 sub and 1 UDF really simple (as Public, to be called from anywhere).

Also, I strongly recommend to use MW initials in every sub/Function you type in MASTER_WB. That way you make 100% sure there is no conflict of names between USER_WB and MASTER_WB calling same procedure. This is just a personal fixation.

In this MASTER_WB, the relevant code is in the workbook object:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
If Application.Workbooks.Count > 2 Then '2 BECAUSE I got also PERSONAL MACRO WORKBOOK
    ThisWorkbook.IsAddin = True
Else
    ThisWorkbook.IsAddin = False
End If
End Sub

Now we save and close MASTER_WB and then in every USER_WB in a module we add the newly created MASTER_WB as a reference:

Notice that in every USER_WB we also type codes to call those public subs and UDF we created in MASTER_WB:

Option Explicit

Sub ESTE_LIBRO()
Call MASTER_UTILITIES.MW_ESTE_LIBRO(ThisWorkbook)
End Sub

Function PORTRES(vRNG As Range) As Double
PORTRES = MASTER_UTILITIES.MW_PORTRES(vRNG)
End Function

And that would be all! Now every USER_WB can access the UDF and subs stored in MASTER_WB. And when we need to make an update/change, we just update MASTER_WB.

If we create a new UDF/SUB, then yes, it needs to be created in both workbooks. MASTER_WB would hold the code itself and every USER_WB needs to add in their modules something like:

Sub MY_NEW_SUB()
Call MASTER_UTILITIES.MY_NEW_SUB
End Sub

Also, if parameters/arguments of a sub/udf change, yes, you also need to update every USER_WB.

But believe me when I say is worth it, at least in our office, because those cases are uncommon. The most usual one is just improving/adding new line of codes, so updating MASTER_WB will make the new code available for all USER_WB connected, without doing it 15 times.

As I said in first line. this solution is not the most elegant one, and for sure there is a better one, but I can tell it works for us. I would like to learn some day about developing add-ins, but for now, we use this amateur fix.

Hope you can adapt this to your needs.