Leading the way in Microsoft Office Development

Step 1 - Create a Workbook ".xls"

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.

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

 (File > Properties)(Summary tab) dialog box

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

 (Tools > VBAProject Properties)(Protection tab)

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.

Step 4 - Save as an Excel Add-in (".xla")

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.

 (File > SaveAs) dialog box

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

Excel 2003 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Addins\

Excel 2002 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Addins\

Excel 2000 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Addins\

Windows NT 4.0 - C:\Winnt\Profiles\"user name"\Application Data\Microsoft\Addins\

Windows 95 and 98 - C:\Windows\Profiles\"user name"\Application Data\Microsoft\Addins\

Windows 95 and 98 - C:\Windows\Application Data\Microsoft\Addins\ (when user profiles are not enabled)

Excel 97 - C:\Program Files\Microsoft Office\Office\Library\

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

Step 5 - Difference between a Workbook and an Add-in

Every workbook has an IsAddin property. This can be viewed from the Workbook Properties window in the Visual Basic Editor.

This is the determining factor between a workbook and an add-in.

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 that 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 useful information that can be used by the add-in.

It is possible to prevent the "Workbook_Open" event from firing for a normal workbook by holding down the Shift key.

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.

You can also very easily convert the add-in back into a regular workbook.

Step 6 - Editing an Add-in

There are no prompts when you edit an Add-in so be careful that you always save your changes before closing Excel.

The advantage of this is that the add-in can edit data that is contained on the worksheets in the add-in.

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.

Step 7 - 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").

Step 8 - Things to Remember

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.