Can you refer to an external macro with excel?

Question

I have a reasonably complex macro that I need to run on multiple different excel sheets, this macro is updated periodically and whenever a change is made its necessary to change it in each individual excel sheet. is there a way to get each excel document to refer to the one macro?

for example if i had a hierarchy like this:

    DOCUMENTS:
       -xlsheet1.xls
       -xlsheet3.xls
       -xlsheet2.xls
       MACROS:
            -macro1.bas

where there was a button in each sheet that ran macro1 when clicked.

Answer 1

The Personal file is good for having a macro across any number of workbooks on a single computer. In a networked environment with multiple users, you could simulate the Personal file by having a single workbook with your macros in it and coding all other workbooks to open and hide this workbook when they start up.

Answer 2

I would recommend either moving that macro to your personal file or create an Add-In

---

Working with Personal File

Topic: Deploy your Excel macros from a central file

Link: http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx

Quote from the above link:

Have you ever wanted to use that really handy macro in all of your Excel worksheets? You can. This column explains how to place your macros in a file called personal.xls and make them available each time you start Excel.

Though it says that it applies to Microsoft Excel 2002 but it applies to all excel versions.

---

Creating an Add-In

Topic: Creating Excel Add-ins

Link: http://www.ozgrid.com/VBA/excel-add-in-create.htm

Quote from the above link:

I am often asked by users 'what is the best way to distribute their macros?' My answer, is without doubt via an Excel Add-in. After all, this is what Add-ins are for. For those that are not sure what an Excel add-in is, it's is nothing more than an Excel Workbook that has been saved as an Add-in, File>Save as \ Microsoft Excel Add-in (*.xla). Once saved and re-opened the Workbook will be hidden and can only be seen in the "Project Explorer" via the Visual Basic Editor. It is NOT hidden in the same way as the Personal.xls as this can be seen (and made visible) via Windows>Unhide.