To create an Excel add-in you must first create an Excel workbook as normal.
Your workbook must contain at least one worksheet but all the worksheets will be hidden when the workbook is converted to an add-in.
The most common use for an add-in is to create user defined worksheet functions. These can be relatively simple to write and are often just several lines of code.
It is highly recommended that you test your code thoroughly before creating the add-in. There will not always be an active sheet, so any references to the "ActiveSheet" will cause an error if there are no workbooks open.
If you are wanting to activate any macros from shortcut keys these must be added before the workbook is converted to an add-in. You will not be able to do this afterwards.
Provide a Name and Description
Always remember to give your add-in a name and description. This can be done by filling in the (File > Properties)(Summary tab).
The name of your add-in is the name that will appear in the (Tools > Add-ins) dialog box. If no name is specified then the file name is used.
The description of your add-in is the short description that will appear at the bottom of the (Tools > Add-ins) dialog box.
The Title box is your Name and the Comments box is your Description.
Protecting your Code
It is often a good idea to protect your code from being viewed and modified. This can be done by password protecting the project.
This will mean that the project will not be expanded in the VBE window unless the correct password is entered.
This can be done by filling in the (Tools > VBAProject Properties)(Protection tab).
To prevent a project from being viewed check the "Lock project for viewing" check box and enter a password.
Try to use a password that you will remember as there is no way to access the code if you forget the password.
The contents of the Confirm Password box and the Password box must match when you press OK or you get an error.
If you do not check the Lock Project for Viewing option but set a password, you will be required to enter a password the next time you open the (Tools > VBAProject Properties) dialog box.
It is probably also worth giving your VBA project a name and description. This can be done from the General tab.
Any passwords and locking that you apply to your project will not take effect until the project is closed and reopened.
Save as an Excel Add-in (".xlam")
You should always recompile your code before saving the workbook as an add-in.
If the code has not been pre-compiled then your add-in will take slightly longer to run the first time.
You can compile your code by selecting (Debug > Compile).
To save your workbook select (File > SaveAs) to display the Save As dialog box.
Select Microsoft Excel Add-in (*.xla) from the "Save as Type" drop-down.
The folder path will change automatically to the default folder path for your add-ins.
Excel 2016 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\
Excel 2013 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\
Excel 2010 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Addins\
Excel 2007 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Addins\
It is possible to save the add-in to a different directory.
You can also specify a different folder path using (Tools > Options)(General tab, At startup, open files in).
Once you are happy with the name of the your add-in and the folder which it will be saved to. Press "OK".
Every workbook has an IsAddin property which defines whether a workbook is just a workbook or an add-in.
This can be viewed from the Workbook Properties window in the Visual Basic Editor.
This property is False for a normal workbook but is automatically set to True when the workbook is saved as an add-in.
When this property is True it means the workbook is always hidden and also means the workbook can be loaded using the (Tools > Add-ins) dialog box.
The advantage of having the worksheets hidden is that they can contain information which the add-in can both use and edit.
It is possible to prevent the "Workbook_Open" event from firing for a normal workbook by holding down the Shift key.
Editing an Add-in
Once the add-in is created you can delete the original workbook file as it is possible to make changes to the add-in directly.
There are no prompts when you edit an Add-in so be careful that you always save your changes before closing Excel.
To display the worksheets within an add-in after it has been created set the "IsAddin" property of the "ThisWorkbook" object to False, after you have made your changes set it back to True.
Converting an Add-in back to a Workbook
It is possible to convert an Excel Add-in back into a workbook.
Change the "IsAddin" property to False and then resave the file using (File > Save As) as a regular workbook (".xls").
Any procedures in an Add-in are not displayed in the (Tools > Macro > Macros) dialog box.
If you want Excel to automatically install your add-in then save it in the default "Add-ins" folder.
When the "IsAddin" property is set to True the workbook is automatically excluded from the Workbooks() collection.
Your comments are not automatically removed when the file is saved as an add-in. Removing all the comments will help to reduce the file size.
Excel add-ins should be as small as possible. The smaller the file size the faster they load.
Even if you protect your VBA code with a password, this method is not full proof as there are lots of programs that can decompile your source code. A password will deter the average user but not anyone who is really determined.