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