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 FunctionFor 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.
Now copy-paste the above code section in the newly created module.
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.
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.
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.
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.
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.
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.
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.
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.
To run this macro just select range then click on copyNumber or press CTRL+SHIFT+C shortcut to copy only numbers.
This feature will be there in all the workbooks.
By saving as add in ,you can also use Excel User Defined function reverseSpell in all the workbooks.
