microsoft excel add in – LearnYouAndMe

In this article, you will learn how to make your own Microsoft Excel Add-in.

What is Excel Add-in?

As name suggests add-in is some program (procedures or macros or VBA UDF) which is added in Excel to enhance its already existing feature or adding new features or functions in the Excel application.

Why make Excel Add-in?

Though Excel covers most features and functions to satisfy users need but sometimes you may want to add some feature or function which is not present in Excel.

By developing an Add-in you make your custom feature or function available in all the workbook.

Write VBA code or record macro

The very first step in making an Excel add-in is to write VBA code or record macro.

Below is the code that I have already written.

The first one is the procedure and the second one is a function (User Defined Function).

Sub copyNumbers()
  'copies numbers only from a selection
  Dim rng As Range
  Set rng = Selection
  On Error Resume Next
  rng.SpecialCells(xlCellTypeConstants, xlNumbers).Copy
End Sub

Function reverseSpell(str As String) As String
  'reverses the spelling of the text
  reverseSpell = StrReverse(str)
End Function

For writing or pasting VBA code press ALT+F11 to open VBA IDE.

In VBA IDE click on the Insert tab then click on Module to create a new standard module.

Creating Standard module in Excel VBA IDE

Now copy-paste the above code section in the newly created module.

Creating Excel Add-in

Create shortcut key for a macro or VBA procedure

In order to use procedures (Subroutine), you should provide some interface to use it like using a shortcut key or by mouse click.

Example:- In Excel you can make a text bold by using CTRL+B or by going in Home tab and clicking on Bold option.

To assign a shortcut key to a copyNumbers procedure. Press ALT+F8 to bring Macro pane.

Alterantively, go to Developer tab then Click on Macro option to bring Macro pane.

Select your VBA procedure (in this case copyNumbers) then click on Options.

Create shrotcut key for a VBA procedure or macro

In the Macros Options pane, you write a shortcut key for a given procedure.

If caps lock key is on then CTRL+SHIFT+letter combination is assigned, if caps lock is off then CTRL+letter combination is assigned.

In the below image I have assigned the CTRL+SHIFT+C shortcut key to copyNumbers Sub.

Press OK button.

Create or assign shortcut key to a macro

To watch the video version click the below link

How to create or change macro/VBA shortcut key

Saving as Excel Add in

After writing VBA code you need to save Excel workbook as an Add in.

Fress F12 or click on Save as option to bring Save as dialog box.

In the save as dialog box, from Save as Type dropdown select Excel Add-in.

This will automatically bring Excel add in location folder, you can change the file name if you want then click on Save.

After saving as add-in close the workbook.

Note:- You can save more than one Sub / VBA procedure or VBA UDF in a single add-in file, no need to create a separate Excel add-in file for different procedures and functions.

Saving Excel workbook as an Excel Add-in

Activating Add in

In order to use Excel add-in saving as Add-in is not enough, you must activate it.

To activate add-in click on the Developer tab then click on Excel Add-ins option this will bring Add-ins pane.

In Add-ins pane list of saved add-ins name will be shown.

If your required add in name is not visible then can click on Browse button then select the required add-in file to make it visible in the list.

Tick on Excel Add-in which you want to activate (in this case My Excel Addin) then click on the Ok button.

Activating Excel Add in

Adding macro in Ribbon tab

To show or add the procedure (macro) in the Ribbon tab, right mouse click on any tab then click on the Customize the Ribbon.

Alternatively, you can click on the File tab then click on Option then click on Customize Ribbon.

Customize Excel ribbon

In Customize ribbon pane click on the New tab button to create a new tab.

Select this new tab and rename (in this case My Addins) it, to some relevant name by clicking on Rename button.

Now rename the group of Custom Tab (My Addins) using Rename button.

Note:- You can create more than one group of a Custom Tab. Just select that custom tab then click on the New Group button. The purpose of the group is to organize similar procedures.

Creating custom tab in Excel ribbon

In Customize Ribbon pane choose Macros from the dropdown list.

Select a Group (in this case CopySpecial present in My Addins tab ) of Customize tab in which you want to add a macro or procedure.

Select a macro or procedure you want to add.

Click on Add button. Click on Ok.

Adding VBA procedure or macro in Excel Ribbon tab
Now, after this setting new tab (My Addins) will be available in all the workbooks and you can use macros by clicking on it or by using the assigned shortcut key.
Custom tab display in Excel ribbon