I have several workbooks that all utilize the same VBA macros. The macros are initiated from buttons on the sheets and then from other macros within the module. If I make a revision to a macro, I must do it on each workbook, taking care to keep the workbook modules identical. I usually do this by changing the module in one workbook and then copy/paste the entire module to the other workbooks. Is there a way to automate this process?

Or is there a better approach?

I have tried to utilize a common workbook for the Module but have not been very successful.

Any advice is welcome. Thanks

I have several workbooks that all utilize the same VBA macros. The macros are initiated from buttons on the sheets and then from other macros within the module. If I make a revision to a macro, I must do it on each workbook, taking care to keep the workbook modules identical. I usually do this by changing the module in one workbook and then copy/paste the entire module to the other workbooks. Is there a way to automate this process?

Or is there a better approach?

I have tried to utilize a common workbook for the Module but have not been very successful.

Any advice is welcome. Thanks

• excel
• vba

• 5 Put the macros in an add-in. Install the add-in in each workbook. Then you only need to make the change in the add-in. – Ron Rosenfeld Commented 16 hours ago
• To elaborate on what Ron said. Open a new workbook. Record a macro but when the prompt appears select "Personal Workbook". You can cancel the macro as long as it starts. In the VBA window you'll see a workbook for PERSONAL.XLSB you can insert your modules here and just close out saving the personal workbook (not the book1) and it will appear available in any sheets YOU open on YOUR MACHINE, it won't carry over in files you send and won't necessarily be saved if you sign on elsewhere depending how things are setup. – Mark S. Commented 15 hours ago
• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented 13 hours ago

1 Answer 1

A clever way is to store them in a separate Excel Add-in (.xlam) file and reference it from the workbooks that need the macros.

Open a new Excel workbook and then press Alt + F11 to open the VBA editor. Now, In the VBA Editor, go to Insert > Module. Finally, paste all your shared macros into this module. For this example, let's say you have a simple macro called MySharedMacro:

Sub MySharedMacro()
    MsgBox "Hello from the Add-in!"
End Sub

You can also just call the macro directly from VBA within this workbook:

Sub CallMyMacroFromAddin()
  Call MySharedMacro
End Sub