26. Creating Add-ins
In This Chapter
Characteristics of Standard Add-ins
Converting an Excel Workbook to an Add-in
Having a Client Install an Add-in
Using a Hidden Workbook as an Alternative to an Add-in
By using VBA, you can create standard add-in files for your clients to use. After the client installs your add-in on his PC, the program will be available to Excel and will load automatically every time that person opens Excel. This chapter discusses standard add-ins.
Be aware that there are two other kinds of add-ins: COM add-ins and DLL add-ins. Neither of these can be created with VBA. To create these types of add-ins, you need either Visual Basic.NET or Visual C++.
Characteristics of Standard Add-ins
If you are going to distribute an application, you might want to package the application as an add-in. Typically saved with an .xlam extension, an add-in offers several advantages:
Usually, clients can bypass your Workbook_Open code by holding down the Shift key while opening the workbook. With an add-in, they cannot bypass the Workbook_Open code in this manner.
After you use the Add-ins dialog to install an add-in (by selecting File, Options, Add-ins, Manage Excel Add-ins, Go), the add-in will always be loaded and available.
Even if the macro security level is set to disallow macros, programs in an installed add-in can still run.
Generally, custom functions work only in the workbook in which they are defined. A custom function added to an add-in is available to all open workbooks.
The add-in does not show up in the list of open files in the Window menu item. The client cannot unhide the workbook by choosing View, Window, Unhide.
Caution
There is one strange rule for which you need to plan. An add-in is a hidden workbook. Because the add-in can never be displayed, your code cannot select or activate any cells in the add-in workbook. You are allowed to save data in your add-in file, but you cannot select the file. Also, if you do write to your add-in file data that you want to be available in the future, your add-in code needs to handle saving the file. Because your clients will not realize that the add-in is there, they will never be reminded or asked to save an unsaved add-in. You might, therefore, add ThisWorkbook.Save to the add-in’s Workbook_BeforeClose event.
Converting an Excel Workbook to an Add-in
Add-ins are typically managed using the Add-ins dialog. This dialog presents an add-in name and description, which you control by entering two specific properties for the file before you convert it to an add-in.
Note
If you’re modifying an existing add-in, you must make it visible before you can edit the properties. See the section “Using the VB Editor to Convert a File to an Add-in” later in this chapter.
To change the title and description shown in the Add-ins dialog, follow these steps:
1. Select File, Info. Excel displays the Document Properties pane on the right side of the window.
2. From the Properties drop-down, select Advanced Properties.
3. Enter the name for the add-in in the Title field.
4. Enter a short description of the add-in in the Comments field (see Figure 26.1).
5. Click the back arrow at the top left of the screen to return to your workbook.
There are two ways to convert a file to an add-in. The first method, using Save As, is easier but has an annoying byproduct. The second method uses the VB Editor and requires two steps, but it gives you some extra control. The sections that follow describe the steps for using these methods.
Using Save As to Convert a File to an Add-in
Select File, Save As. In the Save as Type field, scroll through the list and select Excel Add-in (*.xlam).
As shown in Figure 26.2, the filename changes from filename.xlsm to filename.xlam. Also note that the save location automatically changes to an AddIns folder. The location of this folder varies by operating system, but it will be something along the lines of C:UsersusernameAppDataRoamingMicrosoftAddIns. It is also confusing that, after the .xlsm file is saved as an .xlam type, the unsaved .xlsm file remains open. It is not necessary to keep an .xlsm version of the file because it is easy to change an .xlam back to an .xlsm for editing.
Figure 26.2 The Save As method changes the IsAddin property, changes the name, and automatically saves the file in your AddIns folder.
Tip
If, before selecting the add-in file type, you are already in the folder to which you want to save, just click the back arrow in the Save As window to return to that folder.
Caution
When the Save As method is being used to create an add-in, a worksheet must be the active sheet. The add-in file type is not available if a chart sheet is the active sheet.
Using the VB Editor to Convert a File to an Add-in
The Save As method is great if you are creating an add-in for your own use. However, if you are creating an add-in for a client, you probably want to keep the add-in stored in a folder with all the client’s application files. It is fairly easy to bypass the Save As method and create an add-in using the VB Editor:
1. Open the workbook that you want to convert to an add-in.
2. Switch to the VB Editor.
3. In the Project Explorer, click ThisWorkbook.
4. In the Properties window, find the property called IsAddin and change its value to True, as shown in Figure 26.3.
5. Press Ctrl+G to display the Immediate window.
6. In the Immediate window, save the file, using an .xlam extension, like this:
ThisWorkbook.SaveAs FileName:="C:ClientFilesChap26.xlam", _
FileFormat:= xlOpenXMLAddIn
You’ve now successfully created an add-in in the client folder that you can easily find and email to your client.
Tip
If you ever need to make an add-in visible—for example, to change the properties or view data you have on sheets—repeat the previous steps except select False for the IsAddin property. The add-in becomes visible in Excel. When you are done with your changes, change the property back to True.
Having a Client Install an Add-in
When you email an add-in to a client, have her save it on her desktop or in another easy-to-find folder. You should tell her to follow these steps:
1. Open Excel and select File, Options. The Excel Options dialog appears.
2. In the left navigation pane, select Add-ins.
3. At the bottom of the window, select Excel Add-ins from the Manage drop-down (see Figure 26.4).
4. Click Go. Excel displays the familiar Add-ins dialog.
5. In the Add-ins dialog, click the Browse button.
6. Browse to where you saved the file. Highlight the add-in and click OK.
Note
Excel might prompt you to copy the add-in to its AddIns folder. I do not do this because the folder is hard to find, especially if I need to update the file.
The add-in is now installed. If you allow it, Excel copies the file from where you saved it to the proper location of the AddIns folder. In the Add-ins dialog, the title of the add-in and comments as specified in the File Properties dialog are displayed (see Figure 26.5).
Standard Add-ins Are Not Secure
1. Go to the VB Editor.
2. Select Tools, VBAProject Properties.
3. Select the Protection tab.
4. Select the Lock Project for Viewing check box.
5. Enter the password twice for verification.
If you protect the code and don’t include error handling, users won’t be able to click the Debug button if an error message appears. See chapter 24, “Handling Errors,” for more information on handling errors in code so that the program ends properly and still provides the user with error information that can pass to you.
Closing Add-ins
Add-ins can be closed in three ways:
Clear the add-in from the Add-ins dialog. This closes the add-in for this session and ensures that it does not open during future sessions.
Use the VB Editor to close the add-in. In the VB Editor’s Immediate window, type this code to close the add-in:
Workbooks("YourAddinName.xlam").Close
Close Excel. All add-ins are closed when Excel is closed.
Removing Add-ins
You might want to remove an add-in from the list of available add-ins in the Add-in dialog box. There is no effective way to do this within Excel. Follow these steps:
1. Close all running instances of Excel.
2. Use Windows Explorer to locate the file. The file might be located in %AppData%MicrosoftAddIns.
3. In Windows Explorer, rename the file or move it to a different folder.
4. Open Excel. You get a note warning you that the add-in could not be found. Click OK to dismiss this warning.
5. Select File, Options, Add-ins, Manage Excel Add-ins, Go. In the Add-ins dialog box, clear the name of the add-in you want to remove. Excel notifies you that the file cannot be found and asks whether you want to remove it from the list. Click Yes.
Using a Hidden Workbook as an Alternative to an Add-in
One cool feature of an add-in is that the workbook is hidden. This keeps most novice users from poking around and changing formulas. However, it is possible to hide a workbook without creating an add-in.
It is easy enough to hide a workbook by selecting View, Window, Hide in Excel. The trick is to then save the workbook as Hidden. With a file that is hidden, the normal File, Save choice does not work. Saving the file can be done from the VB Editor window. In the VB Editor, make sure that the workbook is selected in the Project Explorer. Then, in the Immediate window, type the following:
ThisWorkbook.Save
There is a downside to using a hidden workbook: A custom ribbon tab will not be visible if the workbook it is attached to is hidden.
Next Steps
Microsoft has introduced a new way of sharing applications with users: Office Add-ins. These are programs that, simply put, use JavaScript, HTML, and XML to put a web page in a frame on a sheet. Chapter 27, “An Introduction to Creating Office Add-ins,” introduces you to what is involved in creating these apps and deploying them over a network.