An add-in is a program that can be attached to Excel to give it additional functionality.
Once an add-in has been installed its functionality is available from any workbook.
An Excel add-in has the file extension (".xlam") and is a workbook that Excel can open automatically when it starts up.
Add-ins provide an excellent way of increasing the functionality of Excel and are ideal for distributing user defined worksheet functions.
Any workbooks that have been converted to add-ins will have their worksheets hidden.
Add-ins are specific to a particular application. Excel add-ins are specific to Excel and cannot be used with any other application.
What can an Excel Add-in do ?
Add-ins are used to distribute macros and provide an excellent way of increasing the power and functionality of Excel.
You can include anything in an add-in that can be written in code.
Some of the most common uses are listed below:
1) User defined (or custom) worksheet functions can be written to help simplify your formulas.
2) Toolbars and the Menu bar can be customised. Commands can be removed and new ones added.
3) Shortcut Menus can be customised. Commands can be removed and new ones added.
4) Databases can be connected to and data can be extracted or imported.
None of the worksheets within an add-in are visible when the add-in is loaded. Although they can be used for reference within the VBA code.
When changes are made to an add-in you will not be prompted to save your changes when Excel closes.
Any public macros in your code modules will not appear automatically in the (Tools > Macro > Macros) dialog box. They can however be typed in manually and run.
Any function can be referenced (i.e. as a worksheet function) without the need to prefix the workbook name.
Any VBA code added to an add-in will be visible unless a password is added to the VBE project.
It is not possible to bypass the automatic Workbook_Open() event by holding down the Shift key.
The IsAddin workbook property that can be visible when you select the ThisWorkbook object in the VBE is set to True.
Advantages of using an Add-in
Your custom worksheet functions will not have to be preceded by the name of the workbook when you use them in your formulas.
An add-in is easier to distribute and share than a workbook.
Add-ins can be placed in the Excel start up directory meaning that they are loaded automatically when Excel is opened.
If you are the only user of an Excel add-in then the VBA code can be easily edited and saved without the need to open or save extra workbooks.
The Visual Basic code contained in an add-in can be compiled before the workbook is saved. Code in a normal workbook is not compiled until the code is run for the first time. Therefore Visual Basic code contained in an add-in can run slightly faster.
Any event handling procedures cannot be bypassed by using the Shift key. This ensures that an add-in is always initialised.
An add-in is almost invisible to the user, especially if you add a command to an existing drop-down menu. This may be more appropriate for inexperienced users that could get confused by having to open up additional workbooks.
(Advanced tab, At start-up, open all files in) - This can be used as an alternative folder to the xlstart folder.
(Advanced, General, Show add-in user interface errors) - Error messages will be displayed when Excel starts if there are any errors.
The disabled items are stored in registry also HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Resiliency\DisabledItems
When you open a workbook you have the option to hold down the Shift key to prevent any startup macros from running.
This Shift however does not work when you open add-ins
Saving your changes
When you make changes to a workbook and try and close it you are prompted to save your changes.
If you make any changes to the VBA code in an Excel add-in, you will not be prompted to save these changes when Excel closes.
You must always manually save your changes first before closing Excel.
Be aware that the more add-ins you have installed, the longer Excel will take to open. It is worth selecting (Tools > Add-ins) and clearing the check boxes for any add-ins that you do not use.
Any add-ins placed in your xlstart folders will be automatically loaded when Excel is opened.
The majority of add-ins will probably be protected which means that if you try to view the project in the Visual Basic Editor you will be prompted for a password.
You may need to run your setup installation again if you can't find a particular add-in in your (Tools > Add-ins) dialog box.
When searching for installed add-ins also check the 2 XL Start folders: machine folder and personal folder.