Distributing an Excel add-in is a good way to distribute macros. It’s an easy process, but if you choose this route, be sure to use the ThisWorkbook property carefully because it won’t work as expected in the add-in.
VBA’s ThisWorkbook property refers to the workbook that contains the VBA code. That’s why it can seem to fail when used in an add-in; it returns the name of the add-in, not the name of the workbook that’s hosting the add-in. Instead of using ThisWorkbook, use ActiveWorkbook to refer to the hosting workbook instead of the add-in. Of course, you should use ThisWorkbook if you mean to refer to the add-in in your code.
To illustrate this gotcha, we’ll create two similar macros and save the workbook as an add-in. Then, we’ll install the add-in in a second workbook and run both macros to see how they differ.
Creating the add-in
This add-in will contain only two macros. To create the file, open a new workbook and then open the Visual Basic Editor (VBE) by pressing [Alt]+[F11]. Next, do the following:
- From the VBE’s Insert menu, choose Module.
- Add the macros shown below.
Public Sub AddInDemoEx1()
Public Sub AddInDemoEx2()
The next step is to save the workbook as an add-in, as follows:
- Click Save on the VBE Standard toolbar.
- When Excel opens the Save As dialog box, choose Excel Add-in (*.xlam) from the Save As Type dropdown. (Choose Excel 97-2003 Add-in (*.xla) for pre-ribbon versions).
- Enter a name for the add-in.
- At this point, you might want to change the folder. When you save a workbook as an add-in, Excel tries to help by saving the add-in to a special folder. It’s Okay to save it there, but you can control the location by simply changing the folder in the Save In control.
- Click Save (making sure to note the add-in’s location).
With the add-in still open in the VBE, run both macros. Simply position the cursor in a procedure and press [F5] or click Run Sub on the VBE’s standard toolbar. Both will return the same workbook name in a message box.
Installing the add-in
Now, close the add-in and its workbook shell (you don’t have to save the workbook to distribute the macros you just saved as an add-in.) Next, open a new workbook and install the add-in as follows:
- Click the Developer tab and click the Add-Ins option in the Add-Ins group. In Excel 2007, click the Office button and click Excel Options. In the left panel, choose Add-Ins. From the Manage dropdown choose Excel Add-Ins (if necessary) and click Go. In Excel 2003, choose Add-ins from the Tools menu.
- In the resulting dialog, click Browse (if necessary) to locate the add-in you just created. If you saved it to the default folder, you won’t need to do this because this feature defaults to the same folder.
- Check the add-in (if not already checked) and click OK. You just installed the add-in.
To run the add-in’s macros, do the following:
- Open the VBE by pressing [Alt]+[F11].
- In the Project Explorer find the add-in and expand the module if the module isn’t already open (it probably is).
- As before, position the cursor and run each macro.
- Press [F5] or click Run Sub on the VBE’s Standard toolbar.
The ThisWorkbook property in the first macro displays the name of the add-in while the second macro displays the name of the hosting workbook. You can imagine the havoc this difference might cause if you meant to refer to the host workbook and not the add-in! The result could be anything from an obvious error to erroneous data that goes undetected. Both properties are valid in an add-in, but be sure to use the one that refers to the appropriate workbook.
To help with this learning exercise, we have made two example Excel worksheets available. Note these Excel files do include macros.