Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

In this section, I discuss the steps involved in creating a useful add-in. The example uses the Text Tools utility that I describe in Chapter 16.

CD-ROM

The XLSM version of the Text Tools utility (named text tools.xlsm ) is available on the companion CD-ROM. You can use this file to create the described add-in.

Setting up the workbook for the example add-in

In this example, you'll be working with a workbook that has already been developed and debugged . The workbook consists of the following items:

A worksheet named Sheet1 : This sheet is used to hold pre- processed data, which can be restored if the user chooses to undo the operation.

A UserForm named UserForm1 : This dialog box serves as the primary user interface. The code module for this UserForm contains several event handler procedures.

A UserForm named UserForm2: This dialog box is displayed when the workbook is opened. It briefly describes how to access the Text Tools utility. It also contains a Don't Show This Message Again check box.

A VBA module named Module1 : This module contains several procedures, including a procedure that displays the UserForm1 UserForm.

ThisWorkbook code module: This module contains two event handler procedures ( Workbook_Open and Workbook_BeforeClose ) that contain code to create and delete shortcut menu items.

XML code to customize the Ribbon: This customization was done outside of Excel. See Chapter 22 for more information about customizing the Ribbon by using RibbonX.

CROSS-REFERENCE

See Chapter 16 for details about how the Text Tools utility works. The version presented here is a modified version of the application presented in Chapter 16.

Adding descriptive information for the example add-in

To enter a title and description for your add-in, choose Office Prepare Properties, which displays the Document Properties panel below the Ribbon (see Figure 21-3).

Figure 21-3: Use the Document Properties panel to enter descriptive information about your add-in.

Enter a title for the add-in in the Title field. This text appears in list in the Add-Ins dialog box. In the Comments field, enter a description of the add-in. This information appears at the bottom of the Add-Ins dialog box when the add-in is selected.

Adding a title and description for the add-in is optional, but highly recommended.

Creating an add-in

To create an add-in, do the following:

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

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.

About Excel's Add-In Manager
You install and uninstall add-ins by using Excel's Add-Ins dialog box. To display this dialog box, choose Office Excel Options Add-Ins. Then, in the Excel Options dialog box, choose Excel Add-Ins from the Manage drop-down list and click Go. 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:
- XLAM: An Excel 2007 add-in created from an XLSM file
- XLA: A pre “Excel 2007 add-in created from an XLS file
- 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 from within VBA code 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 you start Excel.

Choose Tools xxx Properties (where xxx represents the name of the project) to display the Project Properties dialog box. 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 is changed to TextToolsVBA. This step is optional but recommended.

Save the workbook one last time using its *.XLSM name. Strictly speaking this step is not really necessary, but it gives you an XLSM backup (with no password) of your XLAM add-in file.

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 the file is opened. Click OK.

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

In Excel, choose Office Save As. Excel displays its Save As dialog box.

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

Click Save. A new add-in file is created, and the original XLSM 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

Installing an add-in

To avoid confusion, close the XLSM workbook before installing the add-in created from that workbook.

To install an add-in, do the following:

Choose Office Excel Options Add-Ins. Then, in the Excel Options dialog box, choose Excel Add-Ins from the Manage drop-down list and click Go (or, press Alt+TI). Excel displays the Add-Ins dialog box.

Click the Browse button and locate and double-click the add-in that you just created.

After you find your new add-in, the Add-Ins dialog box displays the add-in in its list. As shown in Figure 21-4, the Add-Ins dialog box also displays the descriptive information that you provided in the Properties dialog box.

Figure 21-4: The Add-Ins dialog box with the new add-in selected.

Click OK to close the dialog box and open the add-in.

When the Text Tools add-in is opened, the Add-Ins tab displays a new group: Text Tools. This group has two controls. In addition, the Text Tools utility adds a new menu item to the shortcut menu that appears when you right-click a range, row, or column.

Testing the add-in

After installing the add-in, it's a good idea to perform some additional testing. For this example, open a new workbook to try out the various features in the Text Tools utility. Do everything you can think of to try to make it fail. Better yet, seek the assistance of someone unfamiliar with the application to give it a crash test.

If you discover any errors, you can correct the code in the add-in (the original file is not required). After making changes, save the file by choosing File Save in the VBE.

Distributing an add-in

You can distribute this add-in to other Excel users simply by giving them a copy of the XLAM file (they don't need the XLSM version) along with instructions on how to install it. If you locked the file with a password, your macro code cannot be viewed or modified by others unless they know the password.

Modifying an add-in

If you need to modify an add-in, first open it and then unlock the VB project if you applied a password. To unlock it, activate the VBE and then double-click its project's name in the Project window. You'll be prompted for the password. Make your changes and then save the file from the VBE (choose File Save).

If you create an add-in that stores its information in a worksheet, you must set its IsAddIn property to False before you can view that workbook in Excel. You do this in the Properties window shown in Figure 21-5 when the ThisWorkbook object is selected. After you make your changes, set the IsAddIn property back to True before you save the file. If you leave the IsAddIn property set to False , Excel will not let you save the file with the XLAM extension.

Figure 21-5: Making an add-in not an add-in.

Creating an Add-In: A Checklist

Before you release your add-in to the world, take a few minutes to run through this checklist:

Did you test your add-in with all supported platforms and Excel versions?

Did you give your VB project a new name? By default, every project is named VBProject . It's a good idea to give your project a more meaningful name.

Does your add-in make any assumptions about the user's directory structure or directory names?

When you use the Add-Ins dialog box to load your add-in, is its name and description correct and appropriate?

If your add-in uses VBA functions that aren't designed to be used in a worksheet, have you declared the functions as Private ? If not, these functions will appear in the Paste Function dialog box.

Did you remember to remove all Debug.Print statements from your code?

Did you force a recompile of your add-in to ensure that it contains no syntax errors?

Did you account for any international issues?

Is your add-in file optimized for speed? See "Optimizing the Performance of Add-Ins" later in this chapter.