How to Create an Add-In from a Normal Excel 2016 Workbook File in VBA
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:
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.
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.
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.
Choose File → Info, and select Show All Properties at the bottom of the right panel.
Excel expands the list of properties displayed.
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.
Choose File → Save As.
In the Save As dialog box, select Excel add-in (*.xlam) from the Save as Type drop-down list.
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.
A copy of your workbook is converted to an add-in and saved with an XLAM extension. Your original workbook remains open.