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
Record Macro dialog box write
Days in Macro name field and assign a Shortcut key
Ctrl + Shift + D and press Ok.
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.
=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:
File tab > Info, and click
Show All Properties at the bottom right:
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.
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:
Protection tab on the dialog box, select the
Lock Project for Viewing check box, and enter a password. Click OK.
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.
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:
Excel Add-insfrom the
Developertab to display the Add-Ins dialog box.
- Select the add-in you just created.
- If the
Brainbelladd-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.
- If the
- Make sure that your new add-in is selected then click
OKto close the 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:
- After installing the add-in, open a new workbook
Ctrl + Shift + Dto execute the Days macro
=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: