How to Create an Add-In from a Normal Excel 2016 Workbook File in VBA - dummies

How to Create an Add-In from a Normal Excel 2016 Workbook File in VBA

By John Walkenbach

Although you can convert any Excel workbook to an add-in, not all workbooks benefit from this conversion. A workbook with no macros makes a useless add-in. In fact, the only types of workbooks that benefit from being converted to an add-in are those with macros. For example, a workbook that consists of general-purpose macros (Sub and Function procedures) makes an ideal add-in.

Creating an add-in isn’t difficult, but it does require a bit of extra work. Follow these steps to create an add-in from a normal workbook file:

  1. Develop your application, and make sure that everything works properly.

    Don’t forget to include a method for executing the macro or macros. You might want to define a shortcut key or customize the user interface in some way. If the add-in consists only of functions, there’s no need to include a method to execute them because they appear in the Insert Function dialog box.

  2. Test the application by executing it when a different workbook is active.

    Doing so simulates the application’s behavior when it’s used as an add-in because an add-in is never the active workbook.

  3. Activate the VBE and select the workbook in the Project window; choose Tools  →  VBAProject Properties and click the Protection tab; select the Lock Project for Viewing check box and enter a password (twice); then click OK.

    This step is necessary only if you want to prevent others from viewing or modifying your macros or UserForms.

  4. Choose File  →  Info, and select Show All Properties at the bottom of the right panel.

    Excel expands the list of properties displayed.

  5. Enter a brief descriptive title in the Title field and a longer description in the Comments field.

    Steps 4 and 5 are not required but make the add-in easier to use, because the descriptions you enter appear in the Add-Ins dialog box when your add-in is selected.

  6. Choose File  →  Save As.

  7. In the Save As dialog box, select Excel add-in (*.xlam) from the Save as Type drop-down list.

  8. Specify the folder that will store the add-in.

    Excel proposes its default add-ins folder (named AddIns), but you can save the file in any folder you like.

  9. Click Save.

    A copy of your workbook is converted to an add-in and saved with an XLAM extension. Your original workbook remains open.