Microsoft Office 2000/Visual Basic Programmer's Guide

Creating an Excel Add-in

You create an Excel add-in by creating a workbook, adding code and custom toolbars and menu items to it, and saving it as an Excel add-in file.

To create an Excel add-in

1. Create a new workbook, add code to it, and create any custom toolbars or menu bars.
   
   
2. On the File menu, click Properties. In the DocumentName Properties dialog box, click the Summary tab, and then use the Title box to specify the name for your add-in, as you want it to appear in the Add-ins dialog box.
   
   
3. Compile the add-in project by clicking Compile VBA Project on the Debug menu in the Visual Basic Editor.
   
   
4. If you want, protect the project from viewing as described in "Securing an Access, Excel, PowerPoint, or Word Add-in's VBA Project" later in this chapter.
   
   
5. Save the add-in workbook as type Microsoft Excel Add-in, which has the extension .xla. By default, Excel add-ins are saved to the C:\Windows\Application Data\Microsoft\AddIns subfolder or, if the system is using user profiles, to the C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns subfolder. This folder is where Excel looks for add-ins when you browse for a new add-in in the Add-ins dialog box. However, if you want the add-in to load automatically when you start Excel, save the add-in to the XLStart folder, as discussed in the following section, "Loading an Excel Add-in."

Important   When you're creating an Excel add-in, pay close attention to the context in which your code is running. When you want to return a reference to the add-in workbook, use the ThisWorkbook property, or refer to the workbook by name. To refer to the workbook that's currently open in Excel, use the ActiveWorkbook property, or refer to the workbook by name.

Once you've saved the add-in, you can reopen it in Excel to make changes to the project. The saved add-in no longer has a visible workbook associated with it, but when you open it, its project is available in the Visual Basic Editor.

Saving the add-in workbook as an Excel add-in sets the IsAddIn property of the corresponding Workbook object to True. You can set this property for the ThisWorkbook object in the Properties window in the Visual Basic Editor; setting it to False makes the workbook visible again.

You can debug an Excel add-in while it's loaded. When you load an add-in, its project appears in the Project Explorer in the Visual Basic Editor. If the project is protected, you must enter the correct password in order to view its code.