In This Chapter
Characteristics of Standard Add-Ins
Converting an Excel Workbook to an Add-In
Having Your Client Install the Add-In
Using a Hidden Workbook as an Alternative to an Add-In
Using VBA, you can create standard add-in files for your clients to use. After the client installs the add-in on his PC, the program will be available to Excel and will load automatically every time he 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++.
If you are going to distribute your applications, you might want to package the application as an add-in. Typically saved with an .xlam
extension for Excel 2007–13 or an .xla
extension for Excel 97–2003, the 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 the Add-Ins dialog is used to install an add-in (select 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.
There is one strange rule for which you need to plan. The 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 data to your add-in file 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 add ThisWorkbook.Save
to the add-in’s Workbook_BeforeClose
event.
Add-ins are typically managed by 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 later section “Using the VB Editor to Convert a File to an Add-In.”
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 side of the window, select Show All 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 arrow at the top left to return to your workbook.
There are two ways to convert the 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 gives you some extra control. The sections that follow describe the steps for using these methods.
Select File, Save As. In the Save as Type field, scroll through the list and select Excel Add-In (*.xlam).
Note
If your add-in might be used in Excel 97 through Excel 2013, choose Excel 97-2003 Add-In (*.xla).
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. This folder location 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.
Tip
If, before selecting the Add-in file type, you were already in the folder to which you want to save, then 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.
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. In the Immediate window, save the file, using an .xlam
extension:
ThisWorkbook.SaveAs FileName:="C:ClientFilesChap26.xlam", _
FileFormat:= xlOpenXMLAddIn
Note
If your add-in might be used in Excel 97 through Excel 2003, change the final parameter from xlOpenXMLAddIn
to xlAddIn
.
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 the add-in visible, for example, to change the properties or view data you have on sheets, repeat the previous steps, except select False
from the IsAddin
property. The add-in becomes visible in Excel. When you are done with your changes, change the property back to True
.
After you email the add-in to your client, have her save it on her desktop or in another easy-to-find folder. She should then follow these steps:
1. Open Excel 2013. From the File menu, select Options.
2. Along the left navigation, 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 your add-in and click OK.
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
Remember that anyone can go to the VB Editor, select your add-in, and change the IsAddin
property to False
to unhide the workbook. You can discourage this process by locking the XLAM project for viewing and protecting it in the VB Editor, but be aware that plenty of vendors sell a password-hacking utility for less than $40. To add a password to your add-in, follow these steps:
1. Go to the VB Editor.
2. From the Tools menu, select VBAProject Properties.
3. Select the Protection tab.
4. Select the Lock Project for Viewing check box.
5. Enter the password twice for verification.
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 VB Editor to close the add-in. In the VB Editor’s Immediate pane, type this code to close the add-in:
Workbooks("YourAddinName.xlam").Close
• Close Excel. All add-ins are closed when Excel is closed.
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 box.
5. Go to 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.
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 Hide from the View, Window menu in Excel. The trick is to then save the workbook as Hidden. Because the file is hidden, the normal File, Save choice does not work. This 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. This isn’t an issue if you use VBA code to create custom menus or toolbars that will appear on the Add-Ins tab.
Microsoft has introduced a new way of sharing applications with users, Apps for Office. These are programs that, simply put, use JavaScript, HTML, and XML to put a web page on a sheet. Chapter 27, “An Introduction to Creating Apps with Office,” introduces you to what is involved in creating these apps and deploying them over a network.