use one macro in different excel files

Question

I wrote a macro, which I would like to use in different excel files, which have almost the same table structure but different data.

So is it possible anyhow just to "include" my macro-script to any excel file?!

I read already this tip but it sounds for me like a bad joke.

Thank you

Answer 1

You need to save the document as an Excel Add-in (.xla) and distribute that. Users can then go Tools>Add-ins to install the add in. They will need to browse to the file you sent them and verify the add-in is checked on the add-in list. Note that add-ins do not show up in Alt+F8 or in Tools>Macro. You need to make a menu for that in your add-in code. see this post http://spreadsheetpage.com/index.php/tip/creating_custom_menus/

If you want your code to be available in all your workbooks, then use your PERSONAL.XLS or in Excel 2007-2010 your PERSONAL.XLSB file. These are normally held in the XLSTART folder under your documents and settings.

This is a hidden workbook that opens when you start Excel. The code you copy in this workbook is available in all workbooks you have opened in Excel.

The easiest way to make one is to record a dummy macro, then select Personal Macro Workbook under the Store The Macro in drop-down list.

Open up macro editor (ALT+F9) and then save the PERSONAL.XLS file. write up a macro, e.g

Public Sub Testing()
    MsgBox "Hey im from Personal.xls"
End Sub

Remember to hide the workbook named personal.xls (Window>>Hide). Now on any workbook, this macro is available.

Answer 2

You have two options; you can

1) include your macro in your personal.xlsb, which will be hidden, yet available for your use or

2) include your macro in an add-in file which will startup with excel every time. Add-in files give you the added benefit of easy distribution if someone else would like to use your macro(s).

For the tool I'm building, I chose to use an add-in file and it's worked out great.

Put your code in a module in the addin, make sure the macro is not private, and you can even make a button for easy use on your quick-access-toolbar.

Problem solved.