Creating Excel Add-Ins

---

An Excel add-in is merely a special type of workbook that includes a set of macros and functions that perform whatever tasks you want.

Creating an Add-In

Suppose you have a worksheet that computes a discounted value based on the list price and the discount. It might look something like the following code:

Public Function DiscountedPrice(ListPrice, Discount) As Currency
 
If Discount <= 1 And Discount >= 0 Then
    DiscountedPrice = ListPrice * (1 - Discount)
   
Else
    DiscountedPrice = 0
 
End If
 
End Function 

Before saving the add-in, you should update the properties of the workbook (by choosing File, Properties from the main menu). At a minimum, you should enter a meaningful value for the title property on the Summary tab. (See Figure 11-2.) This value will be used to identify the add-in in the Add-Ins dialog box. By default, Excel will store add-ins in the Application Data\Microsoft\AddIns directory in the user's Documents and Settings directory.

Figure 11-2: The title of the add-in should be specified along with any other properties of the workbook.

Saving the Add-In

To save the workbook as an add-in, choose File, Save As from the main menu. This will display the Save As dialog box. (See Figure 11-3.) Choose the folder where you want to save the file, and then select Microsoft Office Excel Add-In (*.xla) as the file type. Pressing the Save button will create your Excel add-in.

Figure 11-3: Selecting Microsoft Office Excel Add-In (*.xla) as the file type allows you to save the workbook as an add-in.

Installing the Add-In

Once the add-in has been saved, you can install it by using the Add-Ins dialog box (by choosing Tools, Add-ins from the main menu). When the Add-Ins dialog box is displayed, press the Browse button to locate your newly created add-in. Place a check mark next to the add-in name and press OK to include the add-in in the current workbook. (See Figure 11-4.)

Figure 11-4: Locate your new add-in by looking for the value you specified for the title of the workbook.

Then you can use it in a cell's formula like this:

=DiscountedPrice(D2, E2) 

---