Special Problems with Add-Ins

Add-ins are great, but you should realize by now that there's no free lunch . Add-ins present their share of problems - or should I say challenges? In this section, I discuss some issues that you need to know about if you'll be developing add-ins for widespread user distribution.

Ensuring that an add-in is installed

In some cases, you might need to ensure that your add-in is installed properly: that is, opened using the Add-Ins dialog box and not the Office 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 21-8). Clicking Yes installs the add-in. Clicking No leaves the file open but doesn't install it. Clicking Cancel closes the file.

Figure 21-8: When attempting to open the add-in incorrectly, the user sees this message.

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 Integer    '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 checked) in the Add-Ins dialog box. The user does not see a message.

• The user uses the Add-ins dialog box to install the add-in. The user does not see a message.

• The add-in was opened manually (by using Office Open), and it's 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, and it's a member of the AddIns collection - but it is not installed (not checked). The user sees the message and must take one of the three actions.

By the way, this code can also be used 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 the add-in is installed without a prompt.

CD-ROM  

This add-in, named  check addin.xlam , is available on the companion CD-ROM. Try opening it using both methods (the Add-Ins dialog box and by choosing Office Open).

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 that users will run the application on. 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 Windows\System directory (which might or might not be named Windows\System ). 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 files if they've installed Microsoft's Compatibility Pak. If your add-in uses any features unique to Excel 2007, 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 after the first alphabetic character.

CROSS-REFERENCE  

See Chapter 26 for additional information about compatibility.