Creating an addin

To create an add-in, do the following:

1. Activate the VBE and select the future add-in workbook in the Project window.

2. Choose Debug ^ Compile. This step forces a compilation of the VBA code and also identifies any syntax errors so that you can correct them. When you save a workbook as an add-in, Excel creates the add-in even if it contains syntax errors.

3. Choose Tools ^ xxx Properties to display the Project Properties dialog box (where xxx represents the name of the project). Click the General tab and enter a new name for the project. By default, all VB projects are named VBProject. In this example, the project name was changed to TextToolsVBA. This step is optional but recommended.

4. With the Project Properties dialog box still displayed, click the Protection tab. Select the Lock Project for Viewing check box and enter a password (twice). The code will remain viewable, and the password protection will take effect the next time that the file is opened. Click OK.

If you don't need to protect the project, you can skip this step.

5. Save the workbook by using its *.XLS name. This step is not really necessary, but it gives you an XLS backup of your XLA file.

6. Choose File ^ Save As. Excel displays its Save As dialog box.

About Excel's Add-In Manager

You access Excel's Add-In Manager by choosing the Tools ^ Add-Ins command, which displays the Add-Ins dialog box. This dialog box lists the names of all the available add-ins. Those that are checked are open.

In VBA terms, the Add-In dialog box lists the Title property of each AddIn object in the AddIns collection. Each add-in that appears with a check mark has its Installed property set to True.

You can install an add-in by marking its check box, and you can clear an installed add-in by removing the check mark from its box. To add an add-in to the list, use the Browse button to locate its file. By default, the Add-In dialog box lists files of the following types:

♦ XLL: A standalone compiled DLL file

If you click the Automation button (available only in Excel 2002 and later), you can browse for COM add-ins. Note that the Automation Servers dialog box will probably list many files, and the file list is not limited to COM add-ins that work with Excel.

You can enroll an add-in file into the AddIns collection with the Add method of VBA's AddIns collection, but you can't remove one by using VBA. You can also open an add-in using VBA by setting the AddIn object's Installed property to True. Setting it to False closes the add-in.

The Add-In Manager stores the installed status of the add-ins in the Windows Registry when you exit Excel. Therefore, all add-ins that are installed when you close Excel are automatically opened the next time that you start Excel.

7. In the Save as Type drop-down list, select Microsoft Excel Add-In (*.xla).

8. Click Save. A new add-in file is created, and the original XLS version remains open.

Add-ins can be located in any directory. By default, Excel proposes the following directory:

C:\Documents and Settings\<username>\Application Data\Microsoft\AddIns

0 0

Post a comment