Although you can distribute a macro along with a workbook, if you want to distribute only the macro's functionality, an Excel add-in is the way to go.
An Excel add-in is nothing more than an Excel workbook that was saved as an add-in by selecting File → Save As... → Microsoft Excel Add-in (*.xla). Once it's saved and reopened, the workbook will be hidden and can be seen only in the Project Explorer via the VBE. It is not hidden in the same way as the Personal.xls file, as this can be seen (and made visible) via Windows → Unhide.
Once you have completed the workbook you want to use as an add-in, you need to save a copy of it. You can save it to any location you want, but make sure to note where you placed it.
Open any workbook, and on the Tools menu, select Add-Ins, then click Browse. Locate your add-in from where you saved it, select it, and then click OK.
Ensure that your add-in is in the Add-Ins Available: box and that the box is checked. Then click OK to install the add-in. You can save most code to an Excel add-in without too many changes. There are a few issues worth considering, however:
ThisWorkbook object will always refer to the
add-in, not to the user's workbook. Use the
ActiveWorkbook object instead.
cannot refer to sheets in the
You should always put toolbars, etc., back to the way the user had them originally. There is nothing worse than an add-in that changes all your Excel settings without ...