Creating an Excel Add-in

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 Functions.
It is highly recommended that you test your code thoroughly before creating the add-in.

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 > Info > Properties, Advanced Properties)(Summary Tab).
The name of your add-in is the name that will appear in the 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 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 tick 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 Password box and the Confirm Password box must match when you press OK or you get an error.

If you do not tick 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, Browse) to display the Save As dialog box.
Select Excel Add-in (*.xlam) from the "Save as Type" drop-down.

The folder path will change automatically to the default folder path for your add-ins.

Excel 365 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\ 
Excel 2021 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\ 
Excel 2019 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\ 
Excel 2016 - C:\Users\"user name"\App Data\Roaming\Microsoft\Addins\ 

It is possible to save the add-in to a different directory.
You can also specify a different folder path using (File > Options)(Advanced tab, General, 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 in. Press "OK".

IsAddin Property

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

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 in an add-in after it has been created use the "IsAddin" property of the "ThisWorkbook" object.
Change the "IsAddin" property to False. Make your changes. Change 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").

Important

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.
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.
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.
It is possible to prevent the "Workbook_Open" event from firing for a normal workbook by holding down the Shift key.