COM Add-Ins In Excel 2007

Excel does a good job regarding backwards compatibility. A COM Add-In (CAI) that was created for Excel 2000, Excel XP (2002), or Excel 2003 should work in Excel 2007 without modifications. However, there are a few things to keep in mind when you are developing CAIs that will be used in different versions of Excel.

With version 2007, Excel eliminated the traditional Menu and CommandBar control structure, replacing it with an interface known as the Ribbon. Depending on your "menu" selection (but it is not really a real menu), different groups of Panels will be made visible. (A Panel is a group of buttons with related functionality. In the image below, the Home "menu" is selected and three panels are shown: Clipboard, Font, and Alignment. Back in the development phase, panels were called chunks. I don't know if there is now an official name for these objects.) If you select the Home "menu", you will see one set of panels. If you click the Insert "menu", another group of panels will be made visible. A snapshot of part of the Ribbon is shown below:
Ribbon1

What does this mean with respect to CAI development? It means that any code in the CAI that creates commandbars, commandbar controls, or menu items will no longer work as expected. The Ribbon is customizable only via Excel files in the Excel 2007 file format (xlsx or xlsm) which can contain the necessary XML code used to modify the Ribbon. (A 2007 xlsx or xlsm Excel file is really a Zip file containing 20 or so text XML data files. One of these XML files is used to modify the Ribbon.) Neither a 2003 format (xls) file nor an XLA add-in, nor a COM Add-In can modify the Ribbon. (See Ron de Bruin's Ribbon Customization page for more information.) All customizations, whether in 2003 they are new menu items, controls on existing commandbars, or new commandbars, are displayed in the same Menu Commands or Custom Toolbars panels of the Add-Ins Ribbon view. This mixes your command elements in with the command elements of other add-ins. A snapshot of the Add-Ins Ribbon view is shown below:
Ribbon2

In Excel 2003 and earlier, you would likely put some menu items on the Tools menu, others on the View menu, and perhaps still others on the Data menu. Menu items and commandbar buttons could be placed in the most logical location. In Excel 2007, all customization is put in the Add-Ins view on either the Menu Commands or Custom Toolbars panel. This lack of customization is a sore point with Excel 2007.

Linkage information about a COM Add-In, indicating to Excel that the CAI is available, is stored in one of two areas of the system registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\AddIns (HKCU) or
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\Excel\AddIns (HKLM).

In Excel 2003 and earlier, only CAIs that are configured in HKCU are displayed in the COM Add-Ins dialog. This prevents one user from disabling CAIs that are intended for all users of the machine. In Excel 2003 and earlier, a user can load or unload only CAIs that are configured in HKCU. A user cannot load or unload a CAI that is configured in HKLM.

In Excel 2007, all add-ins, whether configured in HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\AddIns or HKEY_LOCAL_MACHINE\Software\Microsoft\Office\Excel\AddIns are visible in the COM Add-Ins dialog. This means that individual users can disable or unload CAIs that are intended for all users of a machine.

The image below shows the COM Add-Ins dialog in Excel 2003 running on Windows Vista Ultimate.
COMAddInDialog2003

The following image shows the COM Add-Ins dialog in Excel 2007 on the same machine with the same user.
COMAddInDialog2007

As you can see, the dialog in 2007 shows more CAIs that does the 2003 dialog. This is because 2007 displays CAIs that are configured in both HKCU and HKLM while Excel 2003 displays only CAIs configured in HKCU only.