Step-By-Step Create Your First Excel Ribbon Add-in (Easily!)
One of the most prosperous skills I have picked up over the years as a financial analyst has been the ability to create custom Excel add-ins specific to my department and company’s needs. This one skill I have been able to provide to my company has saved numerous people time, frustration, and money. While it took me well over a year to teach myself how to create top-notch add-ins, I want to let you in on a little secret….it’s really NOT THAT HARD! And today I want to share with you just how easy it can be to build an Excel add-in that looks amazing and will provide tremendous value to your company as well as to your professional career.
JUST LAUNCHED!
Check out the My First Add-in template + Online Course which includes templates with many more Ribbon capabilities than the ones available in this article (includes Excel/PowerPoint/Word versions) and also a 6 module online video course to teach you how to customize the Ribbon outside of the template.
In this article, I will walk you through 5 simple steps:
Step 1: Download my free template (I have done all the difficult, nerdy stuff for you in this file)
Step 2: Link your macros and macro descriptions to the Ribbon buttons
Step 3: Test your buttons and make sure they work
Step 4: Choose the icons you want to display (Microsoft provides thousands for free!)
Step 5: Save your template as an add-in file and install/share
And Look What You Can Create!
Step 1: Download The Template
I went ahead and did all the tricky stuff for you and set up a template to give you a great head start. You can download this template by clicking the button below. This will allow you to skip all the difficult Ribbon coding.
After you’ve dowloaded the file, open it up in Microsoft Excel and move on to Step 2!
Step 2: Link Your Macros
Once you’ve opened your brand spanking new Excel Ribbon template, let’s dig into the VBA and link all your macro code snippets so they can be triggered by your Ribbon buttons. I am going to run through an example where I want to create a ribbon with just one macro button. Before we begin, make sure you have the file open and are looking at the Visual Basic Editor (shortcut key Alt + F11).
1. Hide Unused Groups & Buttons
Since I only want to create an add-in with one button and the template holds 50 buttons, I am going to want to hide the other 49 buttons. To do this, I need to navigate to the RibbonSetup module and then down to the GetVisible subroutine. You should see a Case statement that goes through each button name (ie myButton#) and tells the Ribbon whether to show the button (True) or hide it (False). Since I only want one button shown in this example, I’m going to make only the first button (myButton1) have a value of True.
Since the buttons are sectioned off into groups, I can just make the entire group not visible by modifying the Case Statements dealing with group IDs. In the example below I show GroupB not being visible.
2. Add Your Macro Code
Next, let’s add our macro code. I’m just going to use a simple piece of code that does the PasteSpecial command “Pastes Values Only” with the data currently copied to the clipboard.
- Navigate to the Macros module and paste in your macro code.
- Go back to the RibbonSetup module and scroll to the RunMacro subroutine
- Add the macro name to the corresponding button name (overwriting the DummyMacro name)
3. Add A Screentip For Your Macros
A great way to help your users or yourself remember what a button does is to include a Screentip. A Screentip is a brief description that reminds the user what a button does while hovering over it. You see Screentips all the time in the normal Ribbon tabs, but you may have never noticed them. Go ahead and hover over a button on your Home tab and you’ll see some examples.
4. Add Your Tab, Group, & Button Names To The Ribbon UI
To finish off this section we are going to go down to the GetLabel subroutine within the RibbonSetup module. Similar to adding a Screentip, you can add a custom label via this macro that will display beneath your button on the Ribbon.
For this example, let’s call our Tab “Company”, our group “PasteSpecial”, and the button “Paste Values”. As shown below, all we need to do is navigate to the GetLabel subroutine and modify the Labeling variable value to equal the text value we want to be displayed on the Ribbon tab.
At this point, we have linked our macro to a button, we’ve labeled the button, and provided a screentip so our users know what the button does. The major setup pieces are complete. Let’s move on to step 3!
Step 3: Test Your Buttons
This is a brief step but a very important one. After you have linked all your macros to the buttons on your Ribbon, you will want to save your file and close out of it. Re-open the file and see if all your setting tweaks actually flowed into the Macros tab (or in this example the Company tab). Also, start testing your macros to make sure they are all linked to the proper buttons and running as expected.
Step 4: Choose Your Icons
Next, is one of my favorite steps when designing a new add-in, picking out the icons! You might be wondering, how much money am I going to have to spend to get some nice looking icons for my add-in? Well lucky for us, Microsoft has been gracious enough to give everyone complete access to all of their fancy icons used throughout the Office Suite.
So how do we get these awesome icons? Well, remember all that work I did for you to create your handy starting point template? You don’t have to worry about finding the icons at all. All you need to do is tell Microsoft which icons to use by typing out their name in your VBA code. Just navigate to the GetImage subroutine and enter in the icon name with the respective button line. Since our example macro deals with pasting, I am going to use the PasteValues icon.
How Do You Get The Icon Names?
There are a few resources out there that have the Ribbon icon names, but I personally prefer the Excel file Microsoft created called Office 2007 Icons Gallery. This file displays all the icon images in 9 galleries up in the Developer tab of the Ribbon. If you hover over an image, the image’s name will appear in the ScreenTip box. You will need to copy this name verbatim (it is case sensitive!) and add it into the VBA macro called GetImage(), in the respective “case” section. Below is how I found the Page Break icon name.
Let me say it again: the icon name IS CASE SENSITIVE! So make sure you capitalize the correct characters.
How Do You Change The Icon Size?
As you may have noticed when you first opened up the template file, not all the icons are the same size. There are two available sizes that Microsoft allows you to make your icons (large or small). The size of what your icons will be is completely up to you. You may want to make important or heavily used icons large while making other icons small to save space.
To change the size of an icon, navigate to the GetSize() subroutine and simply change your respective buttons to either equal Large or Small.
You will need to save your file and re-open it to implement the changes.
Step 5: Save File As An Add-in
The last step is to save the file as an add-in file. Excel add-in files have a file extension of “.xlam“, so make sure you select that extension type when you are saving. After you have saved your add-in file, you can close your Excel template (the .xlsm file) and install your lovely new add-in! If you don’t know how to install an add-in, you can check out my How to Install a VBA Add-in post that will teach you how to do this.
Congratulations, You Have Developed Your First Add-in!
You’re all done! In just 5 simple steps, you were able to create an awesome and very professional-looking Ribbon-based add-in that you can use for yourself, your team, or even your entire company. Hopefully, I was able to show you that creating add-ins isn’t rocket science but most people don’t know that. Use this to your advantage and use your newly learned skill to impress your boss or even your boss’ boss! If you don’t mind sharing, I’d love to see how your add-in turned out. Feel free to post a screenshot of what you were able to create in the comments section below!
Want More Features? Check Out My First Add-in
I created a more robust add-in creator template that includes the following:
- Over 150 buttons
- Excel, PowerPoint, and Word versions
- Split buttons
- Menu buttons (with up to 12 sub-buttons)
- Dialog Launchers
- Screentips/Supertips
- Online Video Course
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.