Creating An XLA Add In

An XLA Add-In is a certain type of workbook that provides custom functions and/or tools that extend the basic functionality of Excel. An Add-In may contain User Defined Functions (UDFs, see Writing Your Own Functions In VBA) that provide calculation functions specific to your own area of interest or business, functions that extend beyond the normal calculation capability of Excel. An Add-In can also provide tools to manipulate the data in a workbook. Indeed, an Add-In can be written to do nearly anything you want. If it can be done manually, it can be automated with an XLA Add-In.

This page describes XLA Add-Ins written in VBA. For information about other types and uses of Add-Ins, see:

You can download the sample XLA file used as an example in this article.

An XLA Add-In differs from a normal XLS workbook in the following areas:

Create a new, empty workbook and select Save As from the File menu. Enter the name of the Add-In workbook and choose Microsoft Office Excel Add-In (*.xla) near the bottom of the Save as type dropdown box. Excel will change the folder to the standard library path. This is the folder specified by the property Application.UserLibraryPath. The exact location depends on your version of Excel and your version of Windows. You can save the file to that folder or you can navigate in the Open dialog to any folder you wish. (See Where To Install The Add-In below.) Click Save and then press ALT F11 to open the VBA Editor.

If it appears that Excel didn't properly save the add-in, go to the File menu in Excel (not VBA) and choose Open. Navigate to the folder in which you saved the Add-In and open the Add-In file. Go back to the VBA Editor. The XLA project should now be visible in the Project Explorer windows (CTRL R if the window is not visible). This is a known problem in Excel.

The XLA file will not be visible in Excel because Add-Ins are never visible. Everything you need to do is done in the VBA Editor. If your XLA needs to provide any user interaction (that is, it is more than just a library of functions) you need to create command bar items and/or menu items for the user. In general, you should use VBA code to create the command controls when the Add-In is loaded (by putting the code in the Workbook_Open event procedure in the ThisWorkbook code module) and then delete those controls when the Add-In is unloaded (by putting the code in the Workbook_BeforeClose event procedure in the ThisWorkbook code module). Sample code for a complete ThisWorkbook code module is on the sample XLA code page.

Next, add a regular code module to the VBA Project. In that module, insert the code procedures that you assigned to the OnAction property of the menu item. Some very simple code is shown on the sample XLA code page. Of course, this module can contain other code as well. What else goes in the add-in is up to you.

You can store the XLA file in any folder you want. The default location for add-ins is the folder named by the Application.UserLibraryPath property. The value will depend on your version of Excel and your version of Windows. On my Windows Vista Ultimate machine, that path, in both Excel 2003 and 2007, is

C:\Users\Pearson\AppData\Roaming\Microsoft\AddIns

If you save the XLA file in the Application.UserLibraryPath folder, Excel will automatically add the Add-In to the list of available Add-Ins displayed in the Add-Ins dialog box. If you save the XLA to another folder, you will need to click the Browse button in the Add-Ins dialog and navigate to your XLA file. In either case, you will need to check the box next to your add-in to open and load the Add-In.

You should change the file properties to display the proper text in the Add-Ins dialog box. In the Immediate window (CTRL G to display it if it is not visible) in the VBA Editor, change the IsAddIn property to False so that the XLA workbook will be visible in Excel. Enter the following in the Immediate window and press ENTER:

ThisWorkbook.IsAddIn = False

Now, go back to Excel and choose Properties from the File menu. Change the Title property to the text that you want to display in the Add-Ins available list box. Change the Comments property to the text that you want to display in the Add-Ins dialog comment box. Once you have changed the file properties, you need to change the IsAddIn property back to True. Enter the same command in the Immediate window as you entered earlier but change False to True. Finally, save the file.

If you want to make the functionality of your Add-In avaiable to VBA code, you need to change two properties of the VBA Project. With your XLA project active in the VBA Editor, go to the Tools menu and choose VBAProject Properties... and change the Project Name to something meaningful and change the Project Description to some short meaningful description of the Add-In. If desired, you can choose the Protection tab and password protect the VBA code if you don't want others to access the code.

CAUTION: The security implemented by password protecting a VBA Project is extremely weak. Password breakers are widely available. I use Passware's VBAKey program and it can break a VBA password in a matter of seconds. You should think of the password protection as a means to prevent an innocent user from mistakenly breaking something in the code. The security is not strong enough to protect proprietary code or code with intellectual property value. If you need that level of protection, you should be using a COM Add-In, not an XLA Add-In.

Once you have set these properties, other VBA Projects can reference your XLA by setting a reference to the project using the name you provided for the Project Name. To set a reference from a workbook to the add-in, open the file that is to use the reference, go to the Tools menu and choose References. In the dialog that displays, select your add-in project in the list and check that item.

You can download the sample XLA file used as an example in this article.
See also Installing An XLA for other factors involved in deploying and installing XLA Add-Ins on other machines.