Manipulating Add-Ins with VBA

In this section, I present information that will help you write VBA procedures that manipulate add-ins.

Understanding the AddIns collection

The AddIns collection consists of all add-ins that Excel knows about. These add-ins can either be installed or not. The Add-Ins dialog box lists all members of the AddIns collection. Those entries accompanied by a check mark are installed.

ADDING AN ITEM TO THE ADDINS COLLECTION

The add-in files that make up the AddIns collection can be stored anywhere . Excel maintains a partial list of these files and their locations in the Windows Registry. For Excel 2007, this list is stored at

 HKEY_CURRENT_USER\Software\Microsoft\Office.0\Excel\Add-in Manager 

You can use the Windows Registry Editor ( regedit.exe ) to view this Registry key. Note that the standard add-ins that are shipped with Excel do not appear in this Registry key. In addition, add-in files stored in the following directory also appear in the list but are not listed in the Registry:

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

You can add a new AddIn object to the AddIns collection either manually or programmatically by using VBA. To add a new add-in to the collection manually, display the Add-Ins dialog box, click the Browse button, and locate the add-in.

To enroll a new member of the AddIns collection with VBA, use the collection's Add method. Here's an example:

 Application.AddIns.Add "c:\files\newaddin.xlam" 

After the preceding instruction is executed, the AddIns collection has a new member, and the Add-Ins dialog box shows a new item in its list. If the add-in already exists in the collection, nothing happens, and an error is not generated.

If the add-in that you're enrolling is on removable media (for example, a CD-ROM), you can also copy the file to Excel's library directory with the Add method. The following example copies myaddin.xlam from drive E and adds it to the AddIns collection. The second argument ( True , in this case) specifies whether the add-in should be copied . If the add-in resides on a hard drive, the second argument can be ignored.

 Application.AddIns.Add "e:\myaddin.xla", True 

Note  

Enrolling a new workbook in the AddIns collection does not install it. To install the add-in, set its Installed property to True .

Caution  

The Windows Registry does not actually get updated until Excel closes normally. Therefore, if Excel ends abnormally (that is, if it crashes), the add-in's name will not get added to the Registry, and the add-in will not be part of the AddIns collection when Excel restarts.

REMOVING AN ITEM FROM THE ADDINS COLLECTION

Oddly, there is no direct way to remove an add-in from the AddIns collection. The AddIns collection does not have a Delete or Remove method. One way to remove an add-in from the Add-Ins dialog box is to edit the Windows Registry database (using regedit.exe ). After you do this, the add-in will not appear in the Add-Ins dialog box the next time that you start Excel. Note that this method is not guaranteed to work with all add-in files.

Another way to remove an add-in from the AddIns collection is to delete, move, or rename its XLAM (or XLA) file. You'll get a warning like the one in Figure 21-6 the next time you try to install or uninstall the add-in, along with an opportunity to remove it from the AddIns collection.

Figure 21-6: One very direct way to remove a member of the AddIns collection.

AddIn object properties

An AddIn object is a single member of the AddIns collection. For example, to display the filename of the first member of the AddIns collection, use the following:

 Msgbox AddIns(1).Name 

An AddIn object has 14 properties, which you can read about in the Help system. Five of these properties are hidden properties. Some of the terminology is a bit confusing, so I discuss a few of the more important properties in the sections that follow.

THE NAME PROPERTY OF AN ADDIN OBJECT

This property holds the filename of the add-in. Name is a read-only property, so you can't change the name of the file by changing the Name property.

THE PATH PROPERTY OF AN ADDIN OBJECT

This property holds the drive and path where the add-in file is stored. It does not include a final backslash or the filename.

THE FULLNAME PROPERTY OF AN ADDIN OBJECT

This property holds the add-in's drive, path, and filename. This property is a bit redundant because this information is also available from the Name and Path properties. The following instructions produce exactly the same message:

 MsgBox AddIns(1).Path & "\" & AddIns(1).Name MsgBox AddIns(1).FullName 

THE TITLE PROPERTY OF AN ADDIN OBJECT

This hidden property holds a descriptive name for the add-in. The Title property is what appears in the Add-Ins dialog box. This property is read-only, and the only way to add or change the Title property of an add-in is to use the Document Properties panel (choose Office Prepare Properties command). You must use this menu command with the XLSM version of the file before converting it to an add-in.

Typically, a member of a collection is addressed by way of its Name property setting. The AddIns collection is different; it uses the Title property instead. The following example displays the filename for the Analysis ToolPak add-in (that is, analys32.xll ), whose Title property is "Analysis ToolPak" .

 Sub ShowName()     MsgBox AddIns("Analysis Toolpak").Name End Sub 

You can, of course, also reference a particular add-in with its index number if you happen to know it. But in the vast majority of cases, you will want to refer to an add-in by using its Name property.

THE COMMENTS PROPERTY OF AN ADDIN OBJECT

This hidden property stores text that is displayed in the Add-Ins dialog box when a particular add-in is selected. Comments is a read-only property. The only way to change it is to use the Properties dialog box before you convert the workbook to an add-in. Comments can be as long as 255 characters, but the Add-Ins dialog box can display only about 100 characters .

If your code attempts to read the Comments property of an add-in that has no Comments, you get an error.

THE INSTALLED PROPERTY OF AN ADDIN OBJECT

The Installed property is True if the add-in is currently installed - that is, if it is checked in the Add-Ins dialog box. Setting the Installed property to True opens the add-in. Setting it to False unloads it. Here's an example of how to install (that is, open ) the Analysis ToolPak add-in with VBA:

 Sub InstallATP()     AddIns("Analysis ToolPak").Installed = True End Sub 

After this procedure is executed, the Add-Ins dialog box displays a check mark next to Analysis ToolPak. If the add-in is already installed, setting its Installed property to True has no effect. To remove this add-in (uninstall it), simply set the Installed property to False .

Caution  

If an add-in was opened with the Office Open command, it is not considered to be officially installed. Consequently, its Installed property is False .

The ListAllAddIns procedure that follows creates a table that lists all members of the AddIns collection and displays the following properties: Name , Title , Installed , Comments , and Path .

 Sub ListAllAddins()     Dim ai As AddIn     Dim Row As Long     Dim Table1 As ListObject     Cells.Clear     Range("A1:E1") = Array("Name", "Title", "Installed", _       "Comments", "Path")     Row = 2     On Error Resume Next     For Each ai In AddIns         Cells(Row, 1) = ai.Name         Cells(Row, 2) = ai.Title         Cells(Row, 3) = ai.Installed         Cells(Row, 4) = ai.Comments         Cells(Row, 5) = ai.Path         Row = Row + 1     Next ai     On Error GoTo 0     Range("A1").Select     ActiveSheet.ListObjects.Add     ActiveSheet.ListObjects(1).TableStyle = _       "TableStyleMedium2" End Sub 

Figure 21-7 shows the result of executing this procedure.

Figure 21-7: A table that lists all members of the AddIns collection.

CD  

This procedure is available on the companion CD-ROM. The filename is  list add-in information.xlsm .

Note  

You can determine whether a particular workbook is an add-in by accessing its IsAddIn property. This is not a read-only property, so you can also convert a workbook to an add-in by setting the IsAddIn property to True .

And, conversely, you can convert an add-in to a workbook by setting the IsAddIn property to False . After doing so, the add-in's worksheets will be visible in Excel - even if the add-in's VBA project is protected.

Accessing an add-in as a workbook

As I mention earlier, there are two ways to open an add-in file: by using the Add-Ins dialog box and by choosing the Office Open command. The former method is the preferred method for the following reason: When you open an add-in with the Office Open command, its Installed property is not set to True . Therefore, you cannot close the file by using the Add-Ins dialog box. In fact, the only way to close such an add-in is with a VBA statement such as the following:

 Workbooks("myaddin.xlam").Close 

Caution  

Using the Close method on an installed add-in removes the add-in from memory, but it does not set its Installed property to False . Therefore, the Add-Ins dialog box still lists the add-in as installed, which can be very confusing. The proper way to remove an installed add-in is to set its Installed property to False .

As you might have surmised, Excel's add-in capability is a bit quirky, and this component has not been improved in many years . Therefore, as a developer, you need to pay particular attention to issues involving installing and uninstalling add-ins.

AddIn object events

An AddIn object has two events: AddInInstall (raised when the add-in is installed) and AddInUninstall (raised when it is uninstalled ). You can write event handler procedures for these events in the ThisWorkbook code module for the add-in.

The following example is displayed as a message when the add-in is installed:

 Private Sub Workbook_AddInInstall()     MsgBox ThisWorkbook.Name & _       " add-in has been installed." End Sub 

Caution  

Don't confuse the AddInInstall event with the Open event. The AddInInstall event occurs only when the add-in is first installed - not every time it is opened. If you need to execute code every time the add-in is opened, use a Workbook_Open procedure.

CROSS-REFERENCE  

For additional information about events, see Chapter 19.