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 loads 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-10 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 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 codes need 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. You can control these by entering two specific properties for the file before you convert it to an add-in.
To change the title and description shown in the Add-Ins dialog, follow these steps:
There are two ways to convert the file to an add-in. The first method, using Save As, is easier, but has an annoying by-product. 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).
If your add-in might be used in Excel 97 through Excel 2010, choose Excel 97-2003 Add-In (*.xla).
As shown in Figure 27.2, the filename changes from Something.xlsm
to Something.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:Documents and SettingsCustomerApplication DataMicrosoftAddIns. It is also confusing that, after saving the XLSM file 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.
When using the Save As method 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:
IsAddIn
and change its value to True
, as shown in Figure 27.3.
ThisWorkbook.SaveAs FileName:="C:ClientFilesChap27.xlam", FileFormat:= xlO-
penXMLAddIn
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 e-mail to your client.
After you e-mail 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:
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 27.6).
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:
Add-ins can be closed in three ways:
Workbooks("YourAddinName.xlam").Close
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:
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 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
If as authors we’ve done our job correctly, you now have the tools you need to design your own VBA applications in Excel. You understand the shortcomings of the macro recorder yet know how to use it as an aid in learning how to do something. You know how to use Excel’s power tools in VBA to produce workhorse routines that can save you hours of time per week. You’ve also learned how to have your application interact with others so that you can create applications to be used by others in your organization or other organizations.
If you have found any sections of the book that you thought were confusing or could have been spelled out better, we welcome your comments, and they will be given consideration as we prepare the next edition of this book. Write to us:
Whether your goal was to automate some of your own tasks or to become a paid Excel consultant, we hope that we’ve helped you on your way. Both are rewarding goals. With 500 million potential customers, we find that being Excel consultants is a friendly business. If you are interested in joining our ranks, this book is your training manual. Master the topics, and you will be qualified to join the team of Excel consultants.