Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Add-in Designer

Excel's Tools > Add-ins dialog is actually a simple UI that handles the following two registry keys (where the 10.0 indicates the version of Office):

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options

The first key lists the full path of any add-in workbooks that are not in Excel's Library folder (that is, where we've used the Browse button to locate the add-in) and are not installedthey just appear unticked in the Tools > Add-ins list. The second key contains a number of OPENx values, listing the full path for all add-in workbooks that are installedthat is, ticked in the Tools > Add-ins list. These registry entries are covered in more detail in Chapter 24 Providing Help, Securing, Packaging and Distributing. To check for installed add-ins when Excel starts up, it looks for the OPENx entries in the Excel\Options key, opens those workbooks and calls the Auto_Open and/or Workbook_Open procedures therein.Figure 21-1 earlier. When the project is built and the DLL is registered, the Designer writes the registry values into the HKEY_CURRENT_USER key (commonly referred to using its acronym HKCU) to install the add-in for the current user. These values and options are detailed below.

General Tab

For convenience, the General tab of the Add-in Designer is repeated in Figure 21-4.

Figure 21-4. The General Tab of the Designer Dialog

Add-in Display Name

This is the name displayed in Excel's Tools > COMAdd-ins dialog box. It is stored in the registry in the FriendlyName value.

Add-in Description

Excel does not display the description anywhere. If writing a COM Add-in that targets the VBIDE, the description is shown in the VBE's Add-in Manager dialog when the add-in is selected. It is stored in the registry in the Description value.

Application

A drop-down list of all the installed applications that can be extended using COM Add-ins. Selecting this list determines which key is used for the registry entries, such as:

Application Version

A drop-down list of all the installed versions of the selected application. The observant reader might have noticed that when we built our Hello World COM Add-in above, it was immediately working in Excel 2000, 2002 and 2003. This is because the Application Version is ignored for Excel COM Add-ins; the details always get written to the same registry key of HKCU\Software\Microsoft\Office\Excel\Addins. When writing COM Add-ins that target the VBIDE, the version (6.0) is included in the registry key. Of course, we've yet to see anything other than version 6.0 of the VBIDE!

Initial Load Behavior

A set of four choices that control whether and how Excel loads our add-in. The choice is stored in the LoadBehavior value and can be one of the following:

Startup, value = 3:
The add-in is loaded every time Excel starts. This is the most common load behavior setting.

None, value = 0:
The add-in is not loaded and does not appear in the Tools > COMAdd-ins dialog. This option would be selected if the COM Add-in is to be installed for all users. See Installation Considerations later for more details.

Load on Demand, value = 9:
The add-in is loaded the first time one of its menu items is clicked. It is highly unusual for this value to be set within the Designer.

Load at Next Startup Only, value = 16:
The add-in is loaded the first time Excel is started after the add-in is installed, so it can add its menu items permanently to Excel's command bars. Once loaded, Excel changes this value to 9 (Load on Demand), so it is loaded the first time one of its menu items is clicked. See Command Bar Handling later for more details.

Advanced Tab

The Advanced tab of the Add-in Designer is shown in Figure 21-5.

Figure 21-5. The Advanced Tab of the Designer Dialog

Satellite DLL Name

The COM Add-in architecture has been designed to allow easy localization. This means that instead of storing the text of the add-in's name and description in the registry, we can store those strings in a resource table in a separate DLL. We can then use a standard Windows resource editor to translate the text into localized versions of the DLL. In the Designer form, we type in the resource IDs instead of the name and description and provide the name of the DLL containing the resource table in this field (stored as SatelliteDLLName in the registry). When Excel needs to show the Display Name in the Tools > COMAdd-ins dialog, it should recognize that a resource DLL is being used and extract the display name from the resource table. Unfortunately, however, Excel doesn't check for the resource DLL and always displays the meaningless resource ID instead! The use of a satellite DLL in this way works correctly for add-ins targeting the VBIDE.

Registry Key for Additional Add-in Data and Add-in-Specific Data

When the COM Add-in is installed, we have the option of writing additional entries in the registry. These fields specify the entries to write and the registry key where those entries will be written. They are most often used if the COM Add-in is to be installed for all users. See Installation Considerations below for more details.