Excel

Add-ins

An add-in is something added to Excel to give it additional functionality. For example, when you install an add-in, its features blend in with the Excel so they appear to be part of the program and all open workbooks can be use these features. In this tutorial, we’re creating an add-in which is a special type of Excel workbook that has been converted to an add-in file.

An Add-in commonly use VBA macros, event procedures, user-defined functions, and UserForms to make daily tasks faster and easier to accomplish. Following are some unique characteristics of an add-in file:

  • Add-ins open automatically when Excel starts, regardless of the directory in which they’re stored.
  • Add-in files are hidden and cannot be seen.
  • Add-ins provide seamless integration to all open Excel workbooks.
  • Macros and user-defined function contained within the add-in file are available to any of the open workbooks.

Creating An Add-in

Suppose you want to create an add-in that offers a function to write your website name and a macro to write week days through Sunday to Sat. To create an add-in, the first thing you do is open a new workbook and press Alt+F11 to go to the Visual Basic Editor (VBE). To create a custom function, click Insert > Module from the VBE menu bar,and write the following code into newly added module (learn how to create a user-defined function):

Function WebSite()
 WebSite = "https://www.BrainBell.com"
End Function
Custom function

Next, return to Excel program and click Record Macro from the Developer tab (learn how to record a macro).

Developer tab: record macro

In the Record Macro dialog box write Days in Macro name field and assign a Shortcut key Ctrl + Shift + D and press Ok.

Record Macro Dialog Box

Next, write Sun in cell A1, Mon in cell A2 and so on, and click Stop Recording from the Developer tab.

Note: The add-in macros not show in the Macro dialog box so you can not run an add-in macro unless you assigned it a shortcut key.

You’ve created a custom function and a macro, now test your macro and function to see if they are working properly. Press Ctrl + Shift + D shortcut keys which executes the Days macro and write the week days on cells A1 to A7.

Also write =website() on any cell and in result the custom function return https://www.brainbell.com in the current cell.

Adding information to the add-in

Before saving you can (optionally) enter a description of your add-in. Follow these steps to add a description:

Choose File tab > Info, and click Show All Properties at the bottom right:

File info

Enter a title for the add-in in the Title field and add a description in the Comments field. The both field appears in the Add-Ins dialog box, the Title text appears in the list of add-ins and comments appears at the bottom when the add-in is selected.

File info: Show all properties

Assign Password to Protect the VBA Code

If you want to add a password to prevent others from viewing the VBA code, choose Tools > VBAProject Properties:
VBE VBAProject Properties

Click the Protection tab on the dialog box, select the Lock Project for Viewing check box, and enter a password. Click OK.
VBAProject - Project Properties

Saving changes to add-in file

Next, its time to save the file as an add-in, since Excel-2007, the file extension for an add-in is .xlam, for Excel versions prior to 2007, the .xla extension is used. Choose File Tab > Save As > Browse to display the Save As dialog box, select Excel Add-In (*.xlam) from the Save as type drop-down list and write BrainBell in the File name field:

Note: After savign the Add-in, it doesn’t appear in an Excel window. If you made changes in VBA code after saving it as xlam file, you can save the changes from the VBE.
VBE Save File

Installing XLAM Add-in

The easiest way to install an add-in is to use the Add-Ins dialog box, which you can do from any open workbook. Open a new or existing Workbook and install the add-in by following these steps:

  1. Click Excel Add-ins from the Developer tab to display the Add-Ins dialog box.
  2. Select the add-in you just created.
    • If the Brainbell add-in doesn’t appear in the list, close the Excel program and then reopen it.
    • If the add-in is still not in the list then click the browse button to locate the folder where you saved the add-in file, select the filename, and click OK.
  3. Make sure that your new add-in is selected then click OK to close the dialog box.
Add-ins dialog box

The add-in has been installed. Open some existing and new workbooks to test the add-in. Once an add-in installed, it can use with all workbooks as long as it remains selected in the Add-Ins dialog box. An installed add-in opens every time you start Excel but it remains hidden, you can only see its code in the VBE.

Lets test the add-in:

  1. After installing the add-in, open a new workbook
  2. Press Ctrl + Shift + D to execute the Days macro
  3. Write =website() to execute the custom function

Uninstall / Close an add-in

Deselect / un-check the add-in’s name in the Add-Ins dialog box:
Uncheck the add-in