Add-In:creating and using Excel Add-in

Excel add-in adds additional functionality to the Excel. Typical Excel add-ins are Analysis ToolPak and Solver which Microsoft ships along with the Excel.

Without the Analysis ToolPak, you may create your custom program to perform statistical analysis of similar output to the Analysis ToolPak if you use Excel VBA program language. Your custom program's macros are contained in VBA code modules of an Excel Macro-Enabled workbook (*.xlsm). considering that your program is of general-purpose nature, you must want the program to remain installed when any workbooks are opened like the Analysis ToolPak and the Solver.
Your expectation or desire will be fulfilled if you create Excel add-in and can use it across Excel sessions. In addition, you may need to store data to be referred to by your program. The data can be accidentally modified if the data is in another worksheet of .xlsm workbook. You can get around this undesirable case by creating Excel add-in. The worksheet containing data is updated only by your program.

1 Creating Excel add-in
2 Install Excel add-in
3 Download

1 Creating Excel add-in

We base this page's discussion on an Excel add-in discussed at the page of Unit Conversion for engineering and science
Let's take the following steps:

Develop your application using Visual Basic Editor; for example, Unit Conversion.xlsm. The program consists of one userform module and two standard modules containing macros and 49 worksheets holding data for 49 distinct categories.
Include user interface in the .xlsm file. My recommendation is RibbonX user interface using CustomUI editor.

If you want program title and brief description of the program, go to File ⇨ Info and select Properties and click Advanced Properties. Give the name of program in Title field and description in Comments field. The title and description will be viewed in the Add-ins dialog.

If you want to prevent others from viewing or modifying your program, protect your program by your password. To do so, go to the Visual Basic Editor ⇨ References and select VBA project Properties⇨Protection. Check a checkmark for Lock project for viewing followed by entering your password twice.
Save the .xlsm file and go to Debug in the Visual basic editor tool bar and click Compile VBAProjct.
Now we are ready for making Excel add-in. Choose File⇨SaveAs to display the Save As dialog box.
In the Save AS dialog box, select Excel Add-in(.xlam) from the drop-down list. You can save the add-in to any location where you will browse when you install the add-in using Add-ins dialog.
Now you have one .xlsm file and .xlam file.

2. Install Excel add-in to your machine

To Install Excel add-in, take the following steps:

Open an Excel file, new or any existing one.
On the Ribbon select File ⇨ Options
Click Add-ins on Excel Options panel to display View and manage Microsoft Office Add-ins dialog.
Select Excel Add-Ins in the drop down named Manage and click Go to make Add-ins dialog appear.
Find your add-in to install;for example,Unit Conversion in the listdown by scrolling down. If the file is not listed, click Browse to the file you keep to add to the list down.
check the checkmark for the add-in and click OK. You can notice the title and brief description of the program you wrote above.

The add-in is loaded
Go to the tab you created and where you place a control, an icon, to execute the program and you will notice that icon is added.
If you want to view the code, go to the Visual Basic editor and click VBA project of Excel add-in. Now you need to enter your password to view the code.