Centralised Macro Control

[Swift4Play]

I have a collection of workbooks (208 in total), each is unquie in respect to the data they hold but all are identical in respect to the layout and macros contained within.

i was wondering if it is possible to have a workbook (say called SourceCode) which had one complete set of the macros, then every other workbook could just use something like

Application.Run "SourceCode.xls!Macro1"

(but with passing variables)

if possible this would reduce each workbooks size, ensure that each workbook was using identical code and save having to update each workbook should changes need to be made (you'd just update the 'SourceCode').

Can anyone tell me if this is even possible and if so the pros/cons

Kind Regards

 

[AlphaFrog]

If SourceCode.xls is closed, you can use Application.Run to run a macro from within it. This link has code to run a closed workbook macro and pass args.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=279


If SourceCode.xls is open, you could just run the macro or Call it from another macro.

 

[Swift4Play]

Thanks Alpha,

i noticed you stated that if the 'SourceCode' workbook was closed I could use the Application.Run command but the code you directed me states the 'SourceCode' workbook must be open. Is this the case and it has to be open? Only i was hoping to find a solution that wouldn't involve opening it up.

Kind Regards

 

[AlphaFrog]

The closed workbook has to be 1st opened by you or by another macro as far a I know.

 

[AlphaFrog]

Alternatively, you could place your centralized macros in an add-in. The code for an add-in is loaded when you start your Excel application and it would be available to all the workbooks you open.

How to Create an Excel Add-in