Chapter 26. Creating Excel add-ins

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 26 Creating Excel add-ins

In this chapter, you will:

Learn what a standard add-in is
Learn how to create, install, and uninstall an add-in
Use a hidden workbook as an alternative to an add-in

You can create standard add-in files for your clients to use by employing VBA. After the client installs your add-in on her PC, the program will be available to Excel and will load automatically every time she opens Excel. This chapter discusses standard add-ins.

Be aware that there are two other kinds of add-ins: COM add-ins and Office add-ins. Neither of these can be created with VBA. You need either Visual Basic.NET or Visual C++ to create COM add-ins. You use HTML, CSS, and JavaScript to create Office add-ins. Chapter 27, “An introduction to creating Office add-ins,” familiarizes you with the basics of creating Office add-ins.

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 box 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.
Programs in an installed add-in can still run even if the macro security level is set to disallow macros.
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 any part of 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 box. This dialog box 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 box, follow these steps:

Select File, Info. Excel displays the Document Properties pane on the right side of the window.
From the Properties drop-down menu, select Advanced Properties.
Enter the name for the add-in in the Title field.
Enter a short description of the add-in in the Comments field (see Figure 26-1).
Click OK to save your changes.
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 by-product. 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.

The figure shows the Properties dialog box on an add-in. — **FIGURE 26-1** Fill in the Title and Comments fields before converting a workbook to an add-in.

Using Save As to convert a file to an add-in

Select File, Save As, Browse. In the Save As Type field, scroll through the list and select Excel Add-In (*.xlam).

As shown in Figure 26-2, the file name 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.

The figure shows the Save As dialog box. The Save As Type is set for add-ins. The file name is updated to have an .xlam extension and the save to folder is now the AddIns folder. — **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:

Open the workbook that you want to convert to an add-in.
Switch to the VB Editor.
In the Project Explorer, click ThisWorkbook.
In the Properties window, find the property called IsAddin and change its value to True, as shown in Figure 26-3.

FIGURE 26-3 Creating an add-in is as simple as changing the IsAddin property of ThisWorkbook.

Press Ctrl+G to display the Immediate window.
In the Immediate window, save the file, using an .xlam extension, like this:

Click here to view code image
```
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:

Open Excel and select File, Options. The Excel Options dialog appears.
In the left navigation pane, select Add-Ins.
At the bottom of the window, select Excel Add-Ins from the Manage drop-down menu (see Figure 26-4).

FIGURE 26-4 Make sure to select Excel Add-Ins, not COM Add-Ins, from the drop-down menu.
Click Go. Excel displays the familiar Add-Ins dialog box, shown in Figure 26-5.
In the Add-Ins dialog box, click the Browse button.
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 default AddIns folder. In the Add-ins dialog box, the title of the add-in and comments as specified in the File Properties dialog box are displayed (see Figure 26-5).

The figure shows the Add-Ins dialog box. The add-in, TextToValues, is selected. Information about the add-in is shown at the bottom of the dialog box. — **FIGURE 26-5** The add-in is now available for use.

Add-in security

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:

Go to the VB Editor.
Select Tools, VBAProject Properties.
Select the Protection tab.
Select the Lock Project for Viewing check box.
Enter the password twice for verification.

Caution

If you protect the code and don’t include error handling, people 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 customers with error information they can pass to you.

Closing add-ins

Add-ins can be closed in three ways:

Clear the add-in from the Add-Ins dialog box. 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:

Click here to view code image
```
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-Ins dialog box. There is no effective way to do this within Excel. Follow these steps:

Close all running instances of Excel.
Use Windows Explorer to locate the file. The file might be located in %AppData%MicrosoftAddIns.
In Windows Explorer, rename the file or move it to a different folder.
Open Excel. You get a note warning you that the add-in could not be found. Click OK to dismiss this warning.
Select Excel Add-Ins on the Developer tab. 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 beginners 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. The trick is to then save the workbook as Hidden. With a file that is hidden, the normal File, Save choice does not work. You can save the file from the VB Editor’s Immediate 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.

Case study: Using a hidden code workbook to hold all macros and forms

Access developers routinely use a separate database to hold macros and forms. They place all forms and programs in one database and all data in a second database. These database files are linked through the Link Tables function in Access.

For large projects in Excel, I recommend using the same method. You use a little bit of VBA code in the Data workbook to open the Code workbook.

The advantage to this method is that when it is time to enhance the application, you can mail a new code file without affecting the client’s data file.

I once encountered a single-file application rolled out by another developer that the client had sent out to 50 sales reps. The reps replicated the application for each of their 10 largest customers. Within a week, there were 500 copies of this file floating around the country. When they discovered a critical flaw in the program, patching 500 files was a nightmare.

We designed a replacement application that used two workbooks. The data workbook ended up with about 20 lines of code. This code was responsible for opening the code workbook and passing control to the code workbook. As the files were being closed, the data workbook would close the code workbook.

There were many advantages to this method. First, the customer data files were kept to a very small size. Each sales rep now has 1 workbook with program code and 10 or more data files for each customer. As enhancements are completed, we distribute new program code workbooks. The sales rep opens his or her existing customer data workbook, which automatically grabs the new code workbook.

Because the previous developer had been stuck with the job of trying to patch 500 workbooks, we were extremely careful to have as few lines of code in the customer workbook as possible. There are maybe 10 lines of code, and they were tested thoroughly before being sent out. By contrast, the code workbook contains 3,000-plus lines of code. If something goes wrong with the application, I am almost certain that the bad code is in the easy-to-replace code workbook.

In the customer data workbook, the Workbook_Open procedure has this code:

Click here to view code image

Private Sub Workbook_Open()
 On Error Resume Next
 X = Workbooks("Code.xlsm").Name
 If Not Err = 0 then
   On Error Goto 0
   Workbooks.Open Filename:= _
   ThisWorkbook.Path & Application.PathSeparator & "Code.xlsm"
 End If
 On Error Goto 0
 Application.Run "Code.xlsm!CustFileOpen"
End Sub

The CustFileOpen procedure in the code workbook could also handle adding a custom menu for the application. Because custom tabs for hidden workbooks are not visible, you have to use the legacy CommandBars method to create a menu that appears on the Add-Ins tab.

This dual-workbook solution works well and allows updates to be seamlessly delivered to the client without touching any of the 500 customer files.

Next steps

Excel add-ins are a great way to distribute VBA add-ins. But what if your users aren’t allowed to use VBA or you want to distribute your add-in to a wider audience? Microsoft has introduced a new way of sharing applications with customers: 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 introduces you to what is involved in creating these apps and deploying them over a network.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Chapter 26. Creating Excel add-ins

Create new playlist

Sign In

Sign Up

Chapter 26

Creating Excel add-ins

Characteristics of standard add-ins

Converting an Excel workbook to an add-in

Using Save As to convert a file to an add-in

Using the VB Editor to convert a file to an add-in

Having a client install an add-in

Add-in security

Closing add-ins

Removing add-ins

Using a hidden workbook as an alternative to an add-in

Next steps

Table of Contents for
Chapter 26. Creating Excel add-ins