Excel Add Ins

An Excel add-in is a special type of workbook that is usually saved with an .xla file extension. (We will discuss how to create add-ins later in this section.) An add-in can be connected to Excel by checking its check box in the Add-Ins dialog (see Figure 10-5), which is displayed by selecting Add-Ins from the Tools menu.

Figure 10-5. The Add-Ins dialog

Figure 10-5. The Add-Ins dialog

Once an add-in is connected, it remains so (even if Excel is closed and reopened) until the check box in the Add-Ins dialog is unchecked. When connected, an add-in's functionality (VBA procedures) is accessible from any Excel workbook. Thus, it is truly an extension of Excel.

Typically, an add-in contains code that creates new menu items or toolbar items that provide the user with access to the procedures in the add-in. This code is placed in the Workbook_Open event of the add-in so that the menus (or toolbars) are created/customized as soon as the add-in is connected. (We will see examples of this soon.)

10.2.3.1 Creating an add-in

Creating an add-in is a simple process. It begins with an Excel workbook, say SRXUtils.xls. (This stands for Steven Roman's Excel Utilities.) The workbook, of course, contains a number of macros. To create an add-in from the workbook, follow these steps:

1. Compile the project using Excel's VBA Editor.

When the code in any VBA procedure is edited and then executed, Excel must first compile the code; that is, translate the code into a language that the computer can understand. This is why there may be a slight delay the first time code is executed. Subsequent execution of the same code does not require compilation unless the code has been changed since the previous compilation. To compile the code in SRXUtils.xls, select the Compile option from the Debug menu.

2. Set a few worksheet properties and a few project properties.

We should also set a few properties for the add-in. When SRXUtils.xls is the active workbook in Excel, choose the Properties option from the Excel File menu and then display the Summary tab, as shown in Figure 10-6. The Title is the string that will be used in the Add-Ins dialog, shown in Figure 10-7. The Comments will be shown at the bottom of the Add-Ins dialog. Therefore, you should fill in both of these sections in the Properties dialog, as shown in Figure 10-6.

Figure 10-6. Add-in properties

SRXUtilsjifo Properties

General Summary | sotislics | Coments | Custom | Tllte:

Subject:

ftLUhor:

Manager:

Camp^ni:

Cafeg ry :

Keywords:

spxutife rpi ewiments: E*cel Uh lities by =P

Hyperlnk b«e:

Gave preview picHie

Figure 10-7. The Add-Ins dialog

Next, we use Excel's VBA Editor to set the properties of the VBA project. In the Project Explorer of the VBA Editor, select the project whose filename is SRXUtils.xls. Then choose Properties from the Tools menu to display the dialog. Fill in the project name and description as shown in Figure 10-8.

Figure 10-8. VBA project properties

Figure 10-8. VBA project properties

3. Protect the code from viewing.

To protect the code in an Excel workbook from unauthorized viewing, we can use the VBA Project Properties dialog. Selecting the dialog's Protection tab, we get the dialog shown in Figure 10-9. Checking "Lock project for viewing" and entering a password protects the code from viewing (and from alteration). The project will still appear in the VBIDE Project window, but Excel will not allow the user to expand the tree for this project without the password.

Figure 10-9. Protection tab

4. Save the workbook as an add-in in a directory of your choice.

Select the Save As option from the File menu, select "Microsoft Excel Add-In (*.xla)" from the "Save as type" drop-down list, navigate to the directory in which you'd like to save the file, enter the filename in the "File name" drop-down list box (in our example, it's SRXUtils.xla) and press the Save button.

Every Excel workbook has a property called IsAddIn. When this property is True, Excel considers the workbook to be an add-in. One of the consequences of this is that the workbook becomes invisible, so we cannot simply set the IsAddIn property and then save the project as an XLA file, since its workbook will be inaccessible from the Excel user interface. Fortunately, Microsoft realized this and arranged it so that when we save the file as an add-in using the Save As dialog and choosing xla in the "Save as type" dropdown listbox, Excel will automatically change the IsAddIn property value to True. (We can change the value to False as discussed later, in the section, Section 10.2.3.3.)

10.2.3.2 Characteristics of an add-in

An add-in has the following characteristics that set it apart from ordinary Excel workbooks:

• The workbook window and any worksheets in an add-in are hidden from view. The intention is that the creator of the add-in can use worksheets to store supporting data for the add-in. However, this data should not be visible to the user of the add-in. In fact, an add-in is designed to be transparent to the user; both the code and any supporting data are hidden from the user. Thus, if you want your add-in to expose worksheets to the user, they must be placed in separate Excel workbook files, which can be opened by code in the add-in at the desired time.

• As you probably know, when an Excel workbook is changed and the user tries to close the workbook, Excel displays a warning message asking if the user wants to save the changes before closing the workbook. No such message is displayed for an add-in. Thus, the creator of an add-in can change the data in an add-in worksheet through code without worrying that the user of the add-in will be bothered by a message to which he or she could not possibly respond intelligently. (Of course, it is up to the add-in's creator to save any changes if desired, using the Save As method of the Worksheet object.)

• When an Excel workbook is opened, the Workbook_Open event is fired. For an ordinary Workbook, the user can suppress this event by holding down the Shift key. The Open event for an add-in cannot be suppressed. This is in keeping with the tamper-proof nature of add-ins.

• Add-in macros are not displayed in the Macros dialog box, thus hiding them from the user.

0 0

Post a comment