Comparing an add-in with a standard workbook

Any knowledgeable Excel user can create an add-in from an Excel workbook file; no additional software or programming tools are required. You can convert any workbook file to an add-in, but not every workbook is appropriate for an add-in. An Excel add-in is basically a normal XLSM workbook with the following differences:

• The IsAddin property of the ThisWorkbook object is True. By default, this property is False.
• The workbook window is hidden in such a way that it can’t be unhidden by choosing the View ➜ Window ➜ Unhide command. This means that you can’t display worksheets or chart sheets contained in an add-in unless you write code to copy the sheet to a standard workbook.
• An add-in isn’t a member of the Workbooks collection. Rather, it’s a member of the AddIns collection. However, you can access an add-in through the Workbooks collection (see “XLAM file VBA collection membership,” later in this chapter).
• You install and uninstall add-ins by using the Add-Ins dialog box. When an add-in is installed, it remains installed across Excel sessions.
• The Macro dialog box (invoked by choosing Developer ➜ Code ➜ Macros or View ➜ Macros ➜ Macros) doesn’t display the names of the macros contained in an add-in.
• When you write formulas, you can use a custom worksheet function stored in an add-in without having to precede the function’s name with the source workbook’s filename.

Why create add-ins?

You might decide to convert your Excel application into an add-in for any of the following reasons:

• To restrict access to your code and worksheets: When you distribute an application as an add-in and you protect its VBA project with a password, users can’t view or modify the sheets or the VBA code in the workbook. Therefore, if you use proprietary techniques in your application, you can prevent anyone from copying the code — or at least make it more difficult to do so.
• To separate your VBA code from your data: If you send a macro-enabled workbook to a user that contains both code and data, it’s difficult to update the code. The user may have added data or changed existing data. If you send another workbook with update code, the data changes will be lost.
• To make deploying your application easier: You can put an add-in on a network share and have users load it from there. If changes are required, you can replace the add-in on the network share, and when the users restart Excel, the new add-in will load.
• To avoid confusion: If a user loads your application as an add-in, the file isn’t visible and is, therefore, less likely to confuse novice users or get in the way. Unlike a hidden workbook, an add-in can’t be unhidden.

• To simplify access to worksheet functions: Custom worksheet functions stored in an add-in don’t require the workbook name qualifier. For example, if you store a custom function named MOVAVG in a workbook named Newfuncs.xlsm, you must use syntax like the following to use this function in a formula that’s in a different workbook:

  =Newfuncs.xlsm!MOVAVG(A1:A50)
  

  But if this function is stored in an add-in file that’s open, you can use much simpler syntax because you don’t need to include the file reference:

  =MOVAVG(A1:A50)
  

• To provide easier access for users: After you identify the location of your add-in, it appears in the Add-Ins dialog box with a friendly name and a description of what it does.
• To gain better control over loading: Add-ins can be opened automatically when Excel starts, regardless of the directory in which they are stored.
• To avoid displaying prompts when unloading: When an add-in is closed, the user never sees the Do you want to save change? prompt.

Adding descriptive information for the example add-in

To enter a title and description for your add-in, choose File ➜ Info, and choose Advanced Properties from the Properties drop-down.

Enter a title for the add-in in the Title field. This text will appear in the list in the Add-Ins dialog box. In the Comments field, enter a description of the add-in. This information will appear 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:

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

2. 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.

3. 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 ExpCharts. This step is optional but recommended.

4. Save the workbook one last time using its *.XLSM name.

   Strictly speaking, this step isn’t necessary, but it gives you an XLSM backup (with no password) of your XLAM add-in file.

5. 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). Click OK.

   The code will remain viewable, and the password protection will take effect the next time the file is opened. If you don’t need to protect the project, you can skip this step.

6. In Excel, choose File ➜ Save As.

   Excel displays its Save As dialog box.

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

8. Click Save.

A new add-in file is created, and the original XLSM version remains open.

When you create an add-in, Excel proposes the standard add-ins directory, but add-ins can be located in any directory.

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:

1. Choose File ➜ Options, and click the Add-Ins tab.

2. Choose Excel Add-Ins from the Manage drop-down list, and then click Go (or press Alt+TI).

   Excel displays the Add-Ins dialog box.

3. 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 16.5, the Add-Ins dialog box also displays the descriptive information that you provided in the Document Properties panel.

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

When the Export Charts add-in is opened, the Home tab displays a new group, Export Charts, with two controls. One control displays the Export Charts dialog box; the other displays the Help file.

You can use the add-in also by pressing its shortcut key combination: Ctrl+Shift+E.

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 and create some charts to try out the various features in the Export Charts utility. Do everything you can think of to try to make the add-in 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 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 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 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 16.6 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 won’t let you save the file with the XLAM extension.

XLAM file VBA collection membership

An add-in is a member of the AddIns collection but isn’t an official member of the Workbooks collection. However, you can refer to an add-in by using the Workbooks method of the Application object and supplying the add-in’s filename as its index. The following instruction creates an object variable that represents an add-in named myaddin.xlam:

Dim TestAddin As Workbook
Set TestAddin = Workbooks("myaddin.xlam")

Add-ins cannot be referenced by an index number in the Workbooks collection. If you use the following code to loop through the Workbooks collection, the myaddin.xlam workbook isn’t displayed:

Dim w as Workbook
For Each w in Application.Workbooks
    MsgBox w.Name
Next w

The following For-Next loop, on the other hand, displays myaddin.xlam — assuming that Excel “knows” about it — in the Add-Ins dialog box:

Dim a as Addin
For Each a in Application.AddIns
    MsgBox a.Name
Next a

Visibility of XLSM and XLAM files

Ordinary workbooks are displayed in one or more windows. For example, the following statement displays the number of windows for the active workbook:

MsgBox ActiveWorkbook.Windows.Count

You can manipulate the visibility of each window for a workbook by choosing the View ➜ Window ➜ Hide command (in Excel) or by changing the Visible property using VBA. The following code hides all windows for the active workbook:

Dim Win As Window
For Each Win In ActiveWorkbook.Windows
    Win.Visible = False
Next Win

Add-in files are never visible, and they don’t officially have windows, even though they have unseen worksheets. Consequently, add-ins don’t appear in the windows list when you choose the View ➜ Window ➜ Switch Windows command. If myaddin.xlam is open, the following statement returns 0:

MsgBox Workbooks("myaddin.xlam").Windows.Count

Worksheets and chart sheets in XLSM and XLAM files

Add-in files, like normal workbook files, can have any number of worksheets or chart sheets. But to convert an XLSM file to an add-in, the file must have at least one worksheet. In many cases, this worksheet will be empty.

When an add-in is open, your VBA code can access its sheets as if they were in an ordinary workbook. Because add-in files aren’t part of the Workbooks collection, however, you must always reference an add-in by its name and not by an index number. The following example displays the value in cell A1 of the first worksheet in myaddin.xla, which is assumed to be open:

MsgBox Workbooks("myaddin.xlam").Worksheets(1).Range("A1").Value

If your add-in contains a worksheet that you’d like the user to see, you can either copy the sheet to an open workbook or create a new workbook from the sheet.

The following code, for example, copies the first worksheet from an add-in and places it in the active workbook (as the last sheet):

Sub CopySheetFromAddin()
    Dim AddinSheet As Worksheet
    Dim NumSheets As Long
    Set AddinSheet = Workbooks("myaddin.xlam").Sheets(1)
    NumSheets = ActiveWorkbook.Sheets.Count
    AddinSheet.Copy After:=ActiveWorkbook.Sheets(NumSheets)
End Sub

Note that this procedure works even if the VBA project for the add-in is protected with a password.

Creating a new workbook from a sheet within an add-in is even simpler:

Sub CreateNewWorkbook()
    Workbooks("myaddin.xlam").Sheets(1).Copy
End Sub

Accessing VBA procedures in an add-in

Accessing the VBA procedures in an add-in is a bit different from accessing procedures in a normal XLSM workbook. First of all, when you choose the View ➜ Macros ➜ Macros command, the Macro dialog box doesn’t display the names of macros that are in open add-ins. It’s almost as if Excel were trying to prevent you from accessing them.

Because procedures contained in an add-in aren’t listed in the Macro dialog box, you must provide other means to access them. Your choices include direct methods (such as shortcut keys and Ribbon commands) as well as indirect methods (such as event handlers). One such candidate, for example, may be the OnTime method, which executes a procedure at a specific time of day.

You can use the Run method of the Application object to execute a procedure in an add-in. For example:

Application.Run"myaddin.xlam!DisplayNames"

Another option is to use the Tools ➜ References command in VBE to enable a reference to the add-in. Then you can refer directly to one of its procedures in your VBA code without the filename qualifier. In fact, you don’t need to use the Run method; you can call the procedure directly as long as it’s not declared as Private. The following statement executes a procedure named DisplayNames in an add-in that has been added as a reference:

Call DisplayNames

Function procedures defined in an add-in work just like those defined in an XLSM workbook. They’re easy to access because Excel displays their names in the Insert Function dialog box under the User Defined category (by default). The only exception is if the Function procedure was declared with the Private keyword; then the function doesn’t appear there. That’s why it’s a good idea to declare custom functions as Private if they will be used only by other VBA procedures and aren’t designed to be used in worksheet formulas.

You can use worksheet functions contained in add-ins without the workbook name qualifier. For example, if you have a custom function named MOVAVG stored in the file newfuncs.xlsm, you’d use the following instruction to address the function from a worksheet in a different workbook:

=newfuncs.xlsm!MOVAVG(A1:A50)

But if this function is stored in an add-in file that’s open, you can omit the file reference and write the following instead:

=MOVAVG(A1:A50)

Keep in mind that a workbook that uses a function defined in an add-in will have a link to that add-in. Therefore, the add-in must be available whenever that workbook is used.

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 2016, this list is stored at:

HKEY_CURRENT_USERSoftwareMicrosoftOffice16.0ExcelAdd-in Manager

You can use the Windows Registry Editor (regedit.exe) to view this Registry key. Note that the standard add-ins 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 aren’t listed in the Registry:

C:Program FilesMicrosoft Office ootOffice16Library Note that the path on your system may be different depending on the version of Windows you are using. You can add a new AddIn object to the AddIns collection either manually or programmatically. 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 add a new member to the AddIns collection with VBA, use the collection’s Add method. Here’s an example:

Application.AddIns.Add"c:files
ewaddin.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 isn’t generated.

If the add-in 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

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 doesn’t 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 won’t appear in the Add-Ins dialog box the next time that you start Excel. Note that this method isn’t 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 16.7 the next time you try to install or uninstall the add-in, along with an opportunity to remove it from 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 15 properties, which you can read about in the Help system. Of these properties, 5 are hidden. Some of the terminology is a bit confusing, so I discuss a few of the more important properties in the sections that follow.

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

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

The Installed property of an AddIn object

The Installed property is True if the add-in is currently installed — that is, if it has a check mark 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.

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 Application.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 16.8 shows the result of executing this procedure. If you modify the code to use the AddIns2 collection, the table will also include add-ins that were opened using the File ➜ Open command (if any). The AddIns2 collection is available only in Excel 2010 and later.

Accessing an add-in as a workbook

You can open an XLAM add-in file by using the Add-Ins dialog box or by choosing the File ➜ Open command. The former method is the preferred method for the following reason: When you open an add-in with the File ➜ Open command, its Installed property is not set to True. Therefore, you can’t 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

As you may have surmised, Excel’s add-in capability is quirky. This component (except for the addition of the AddIns2 collection) hasn’t 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 (occurs when the add-in is installed) and AddInUninstall (occurs 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

Ensuring that an add-in is installed

In some cases, you may need to ensure that your add-in is installed properly — that is, opened using the Add-Ins dialog box and not the File ➜ Open command. This section describes a technique that determines how an add-in was opened and gives the user an opportunity to install the add-in if it is not properly installed.

If the add-in isn’t properly installed, the code displays a message (see Figure 16.9). Clicking Yes installs the add-in. Clicking No leaves the file open but doesn’t install it. Clicking Cancel closes the file.

The code that follows is the code module for the add-in’s ThisWorkbook object. This technique relies on the fact that the AddInInstall event occurs before the Open event for the workbook.

Dim InstalledProperly As Boolean
 
Private Sub Workbook_AddinInstall()
    InstalledProperly = True
End Sub
 
Private Sub Workbook_Open()
   Dim ai As AddIn, NewAi As AddIn
   Dim M As String
   Dim Ans As Long
   'Was just installed using the Add-Ins dialog box?
   If InstalledProperly Then Exit Sub
 
   'Is it in the AddIns collection?
   For Each ai In AddIns
      If ai.Name = ThisWorkbook.Name Then
         If ai.Installed Then
             MsgBox"This add-in is properly installed.", _
               vbInformation, ThisWorkbook.Name
             Exit Sub
         End If
      End If
   Next ai
 
    'It's not in AddIns collection, prompt user.
    M ="You just opened an add-in. Do you want to install it?"
    M = M & vbNewLine
    M = M & vbNewLine &"Yes - Install the add-in."
    M = M & vbNewLine &"No - Open it, but don't install it."
    M = M & vbNewLine &"Cancel - Close the add-in"
    Ans = MsgBox(M, vbQuestion + vbYesNoCancel, _
      ThisWorkbook.Name)
    Select Case Ans
        Case vbYes
            ' Add it to the AddIns collection and install it.
            Set NewAi = _
              Application.AddIns.Add(ThisWorkbook.FullName)
            NewAi.Installed = True
        Case vbNo
            'no action, leave it open
        Case vbCancel
            ThisWorkbook.Close
    End Select
End Sub

The procedure covers the following possibilities:

• The add-in was opened automatically because it’s an installed add-in listed (and displaying a check mark) in the Add-Ins dialog box. The user doesn’t see a message.
• The user uses the Add-Ins dialog box to install the add-in. The user doesn’t see a message.
• The add-in was opened manually (by using File ➜ Open) and is not a member of the AddIns collection. The user sees the message and must take one of the three actions.
• The add-in was opened manually, is a member of the AddIns collection, but is not installed (not displayed with a check mark). The user sees the message and must take one of the three actions.

By the way, you can also use this code as a way to simplify the installation of an add-in that you give to someone. Just tell them to double-click the add-in’s filename (which opens it in Excel) and respond Yes to the prompt. Better yet, modify the code so that the add-in is installed without a prompt.

Referencing other files from an add-in

If your add-in uses other files, you need to be especially careful when distributing the application. You can’t assume anything about the storage structure of the system on which users will run the application. The easiest approach is to insist that all files for the application be copied to a single directory. Then you can use the Path property of your application’s workbook to build path references to all other files.

For example, if your application uses a custom help file, be sure that the help file is copied to the same directory as the application itself. Then you can use a procedure like the following to make sure that the help file can be located:

Sub GetHelp()
    Application.Help ThisWorkbook.Path &"userhelp.chm"
End Sub

If your application uses Application Programming Interface (API) calls to standard Windows DLLs, you can assume that these can be found by Windows. But if you use custom DLLs, the best practice is to make sure that they’re installed in the WindowsSystem directory (which might or might not be named WindowsSystem). You’ll need to use the GetSystemDirectory Windows API function to determine the exact path of the System directory.

Detecting the proper Excel version for your add-in

As you may know, those who use an earlier version of Excel can open Excel 2007 (and later) files if they’ve installed Microsoft’s Compatibility Pak. If your add-in uses any features unique to Excel 2007 or later, you’ll want to warn users who attempt to open the add-in with an earlier version. The following code does the trick:

Sub CheckVersion()
    If Val(Application.Version) < 12 Then
        MsgBox"This works only with Excel 2007 or later"
        ThisWorkbook.Close
    End If
End Sub

The Version property of the Application object returns a string. For example, this might return 12.0a. This procedure uses VBA’s Val function, which ignores everything beginning with the first non-numeric character.