Simplest Way to Create Excel Add-in
Simplest Way to Create Excel Add-in
In this article, we are going to talk about how you can create Excel Add-in by following few simple steps. Also we will talk about how you can link the Excel Add-in at your Excel Application open event and add it in Excel Ribbon.
Table of Content
- What is an Excel Add-in
- How you can create your own Simple Excel Add-in
- Step 1: Write Your VBA Code
- Step 2: Save Your Excel Add-in File
- Step 3: Configure the Add-in on Excel Open Event
- Step 4: Add the Add-in in Excel Ribbon
- Step 5: Let's Use the Add-in
- How you can create Advanced Excel Add-in with Ribbon
- Step 1: Write Your VBA Code
- Step 2: Save Your Excel Add-in File
- Step 3: Configure the Add-in on Excel Open Event
- Step 4: Let's Use the Add-in
- Add Custom Image as Button Icon
- Sample XML Codes for Creating Controls in Ribbon
- ButtonGroup
- CheckBox
- ComboBox
- DropDown
- EditBox
- LabelControl
- Menu
- ToggleButton
- Practice file
What is an Excel Add-in
In simple words, Excel Add-in helps you to extend the features of Excel application. Using Excel Add-in, you can perform custom actions in Excel such as formatting the data, doing complex calculations which are not possible through Excel formulas, Reading or Writing data in other Excel files and so many more actions.
Not just Excel actions, you can also interact with other application’s object models right from your Excel file. Such as sending emails through Outlook, Generating customer letters in word file, Performing File and Folder operations etc.
How You Can Create Your Own Excel Add-in
Few developers thinks that creating an Excel Add-in is very difficult. However Excel Add-in is as simple as creating a normal VBA Macro. Let’s see how you can create an Excel Add-in.
Step 1: Open a new Excel file and start writing your VBA code for the functionalities which you want to be available in your Excel Add-in
For the purpose of demo, here we have written a code which reads To, Cc, Subject and Email body from the active row of the sheet and displays the drafted Outlook email to user. Excel Add-ins are also compatible with userforms; hence you can also design userforms and allow users to navigate through the Add-in using the forms.
Step 2: This step defines how your Excel Add-in will appear on Excel Menu bar. There are two ways an Excel Add-in may appear on Excel as shown in below screenshots.
First Way:
Second Way:
Here our target is to show the simplest way to create Excel Add-in; hence we will be using first way.
If you are looking for ribbon based Excel Add-in then you can directly jump on How you can create Advanced Excel Add-in with Ribbon section.
Now it’s time to save our file. To save any Excel file as Add-in, we need to save it in .xlam (Excel Add-In) format.
Note that by default Excel Add-ins are saved in AddIns folder under C:\Users\XXXX\AppData\Roaming\Microsoft. However you can save them at other places as well.
Once the file is saved, please close the same before proceeding with next step.
Step 3: Now as we have already saved our Excel Add-in, it’s time to use it. For this, we will open Excel application and go to Options under File menu.
In ‘Add-Ins’ tab, select ‘Excel Add-ins’ from Manage dropdown and click on ‘Go’ button
You should now be able to see the add-in we have just saved with ‘MyFirstAddin’ name in the list. If the add-in is not visible in the list, use the browse button to add the same.
Now the add-in is linked with Excel application open event; hence it will be automatically opened or activated as soon as you open any Excel file.
Step 4: The only step left now is to add the navigation button to run the macro. For this, right click on the menu bar and click on ‘Customize Quick Access Toolbar…’
Select ‘Macros’ from commands dropdown
Select the macro named ‘DraftEmail’ and click on ‘Add >>’ button
Click on ‘OK’ button and you are ready to use the Add-in
You will notice that a new icon is added in your Excel Quick Access Toolbar
Step 5: Let’s use the Add-in now. For this, we will add some dummy data in any opened Excel file
Select any cell in second row and click on the Add-in Macro
The Add-in macro will read the details from active row of the active Excel file and draft the email like below
How you can create Advanced Excel Add-in with Ribbon?
Some developers have also requested us to share steps involved to create Add-in with ribbon menu. Let’s have a look at how you can create an Excel Add-in with custom defined menu ribbon.
Step 1: Open a new Excel file and start writing your VBA code for the functionalities which you want to be available in your Excel Add-in
For the purpose of demo, here we have written a code which reads To, Cc, Subject and Email body from the active row of the sheet and displays the drafted Outlook email to user. Excel Add-ins are also compatible with userforms; hence you can also design userforms and allow users to navigate through the Add-in using the forms.
We are expecting our Add-in with a ribbon named ‘My Ribbon’ and a button to execute the code just like below
As you can see in the above screenshot, there is a button in the ribbon that is expected to draft the email. For the button to work, we need to add the event handler in our VBA code. For this, we need to add below VBA code in our Add-in
Public Sub Outlook_VBA_Code(rib As IRibbonControl)
Call DraftEmail
End Sub
Now it’s time to save our file. To save any Excel file as Add-in, we need to save it in .xlam (Excel Add-In) format.
Note that by default Excel Add-ins are saved in AddIns folder under C:\Users\XXXX\AppData\Roaming\Microsoft. However you can save them at other places as well.
Once the file is saved, please close the same before proceeding with next step.
Step 2: Next step is to add customized menu in the Excel file. For this, we need to download and install OfficeCustomUIEditorSetup application. You can download it from below link or you can also find it on internet easily.
Once you have downloaded and installed the application, you need to open the Excel file in the application
Right click on the file name and select ‘Office 2007 Custom UI Part’
Paste the following XML code in the editor section
<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”> <ribbon> <tabs> <tab id=”CustomRibbon1″ label=”My Ribbon” insertAfterMso=”Data”> <group id=”Group1″ label=”My Custom Ribbon Add-in”> <button id=”btnDraftEmail” label=”Draft Email” size=”large” onAction=”Outlook_VBA_Code” imageMso=”MicrosoftOutlook” /> </group> </tab> </tabs> </ribbon> </customUI>
You may have noticed that we have set imageMso property to ‘MicrosoftOutlook’ in the above XML code. Here imageMso property is used to set the image/icon of the button. You may refer below links to view the list of available icon names that can be used:
https://bert-toolkit.com/imagemso-list.html
https://www.spreadsheet1.com/office-excel-ribbon-imagemso-icons-gallery-page-01.html
Save the code and exit from the application
Step 3: Now as we have already saved our Excel Add-in, it’s time to use it. For this, we will open Excel application and go to Options under File menu
In ‘Add-Ins’ tab, select ‘Excel Add-ins’ from Manage dropdown and click on ‘Go’ button
You should now be able to see the add-in we have just saved with ‘MyRibbonAddin’ name in the list. If the add-in is not visible in the list, use the browse button to add the same.
Now the add-in is linked with Excel application open event; hence it will be automatically opened or activated as soon as you open any Excel file.
Step 4: Let’s use the Add-in now. For this, we will add some dummy data in any opened Excel file
Select any cell in second row and click on the ‘Draft Email’ button under ‘My Ribbon’
The Add-in macro will read the details from active row of the active Excel file and draft the email like below
Add Custom Image as Button Icon
Step 1: Open the Excel file in Custom UI Editor Application
Step 2: Click on ‘Icons’ under Insert menu
Step 3: Select your custom icon and click on ‘Open’ button
Step 4: You will notice that the icon is added under customUI.xml
Step 5: Now it’s time to change the XML code to use the custom icon added by us as button icon. For this, remove the imageMso property of button and set image property to mybutton (icon name)
Step 6: Save the code and exit from the application
Step 7: Here is how our ribbon looks like with custom image as button icon
Sample XML Codes for Creating Checkbox, Dropdown and Other Controls in Ribbon
In the above article, we have used a button to create the ribbon. To help our subscribers, we are sharing few more XML codes for reference which can used to create different types of controls in a Ribbon such as ButtonGroup, CheckBox, ComboBox, DropDown, EditBox, Label, Menu and ToggleButton.
buttonGroup (Button Grouping Container)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Button Group">
<buttonGroup id="buttonGroup1">
<button id="button1" imageMso="Bold" onAction="Button1ClickEvent"/>
<button id="button2" imageMso="Italic" onAction="Button2ClickEvent"/>
<button id="button3" imageMso="Underline" onAction="Button3ClickEvent"/>
</buttonGroup>
</group>
</tab>
</tabs>
</ribbon>
</customUI>checkbox (Check Box)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<checkBox id="checkBox" label="CheckBox" onAction="ClickEvent" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>comboBox (Combo Box)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<comboBox id="comboBox" label="Combo Box" onChange="ClickMe">
<item id="item1" label="Item 1"/>
<item id="item2" label="Item 2"/>
<item id="item3" label="Item 3"/>
</comboBox>
</group>
</tab>
</tabs>
</ribbon>
</customUI>dropDown (Dropdown Control)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<dropDown id="dropDown" label="DropDown" onAction="ClickEvent">
<item id="item1" label="Item 1" />
<item id="item2" label="Item 2" />
<item id="item3" label="Item 3" />
<button id="button" label="Button" onAction="ClickEvent_2" />
</dropDown>
</group>
</tab>
</tabs>
</ribbon>
</customUI>editBox (Edit Box)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<editBox id="editBox" label="Edit Box" onChange="ClickEvent"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>labelControl (Text Label)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<labelControl id="label" label="Label Control" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>menu (Menu)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<menu id="menu" label="Menu" imageMso="HappyFace" >
<button id="button1" label="Button 1" imageMso="FileSave" onAction="Bultton1ClickEvent" />
<button id="button2" label="Button 2" imageMso="Bold" onAction="Bultton2ClickEvent" />
<button id="button3" label="Button 3" imageMso="Undo" onAction="Bultton3ClickEvent" />
</menu>
</group>
</tab>
</tabs>
</ribbon>
</customUI>toggleButton (Toggle Button)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomRibbon1" label="My Ribbon" insertAfterMso="Data">
<group id="Group1" label="Custom Group">
<toggleButton id="toggleButton" label="Toggle Button" onAction="ClickEvent" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
