Making an Excel macro apply to all worksheets

Excel and Word seem to be set up so that macros are strictly local to a particular document. Is there any way I can save a macro so that it will work in any document I load? To one of the program-wide libraries perhaps? Or something like a permanent user-defined function?

One way to do this is to:

-Open a new Excel workbook.
-Define your macro in this workbook.
-Save the workbook in XLA format.
-Close to workbook.
-Go to Tools, Add-ins, then browse for the file you just saved.

An XLA file is just a spreadsheet that is saved so it can be used as an add-in. Once you load it as an add-in, its macros are available to any other workbook, although that’s only good for the machine where you loaded the add-in. If you want to distribute a workbook using one of those macros, you need to distribute the add-in too.

Cookin’s answer is probably best, but there are some interesting uses of the “Startup Folder” in Excel - link

Thanks everyone!

K364 points to an important feature.

I define macros in PERSONAL.XLS which is in the startup folder, but those macros have to be called with a fully qualified name. I used them to define custom toolbar buttons, but it is a bit clunky when you have to use it in a formula.

I was unaware of the **Book.xlt ** technique. That would be another way to define macros that automatically become part of every workbook you create, which also solves the distribution problem I mentioned above. However, it does cause a problem if you need to update a macro (say, if you found a bug), because you would have to update it in every workbook you have, not just one.

What if I didn’t create the workbook in question? I’m editing copies of workbooks I’m accessing on my office network.

The XLA technique will work. But be aware that if you use one of your own macros to create a custom function that is used in the workbook, and then store it back to your office network, it won’t work for anyone else but you.

No problem. What I’m doing incidentally is simply taking the numerical contents of two cells and writing their sum to another cell. That cell has to be a directly entered value, NOT a function. The macro in question gets the sum that displays on your status bar next to the NUM at the bottom of your page, and copies it to the Clipboard. I was astonished that Excel had no built-in way to do this already.


Are you copying the sum function to another cell?
You can do the same thing by copying, then right click --> paste special --> paste value.

Also, if you record your macro choose store macro in personal.xls or personal macro workbook. This will allow you to use the macro in any workbook. you can even hotkey it.

No, I have two cells with numerical values in them. I need the sum of those two values in another cell, and that third cell has to have an literal value, not a function. And I need to do this ~200 times per workbook. Selecting the two cells automatically displays their sum on the taskbar- I just need an automatic way of getting that value into the Clipboard so I can paste it.