O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #94. Distribute Macros

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:

  • The ThisWorkbook object will always refer to the add-in, not to the user's workbook. Use the ActiveWorkbook object instead.

  • You cannot refer to sheets in the ActiveWorkbook with CodeNames.

  • 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required