My First Custom Ribbon Using Excel-DNA And Visual Studio

For the next instalment in this “getting started with Excel-DNA for VBA’ers ” mini-series I’m going to have a look at customisation of the Office ribbon. The end goal of this post is very simple: a custom tab with a button which shows a message box.

First let’s set up a new Excel-DNA project.

In Visual Studio press CTRL+SHIFT+N, choose a Visual C# Class Library project and give it the name/solution name MyFirstRibbon, like so:

1stribbonprojectsetup

Click on OK and then type the following command into the package manager console and press ENTER:

Install-Package Excel-DNA

If the package manager console window isn’t already showing in Visual Studio then you can get to it via the View > Other Windows menu.

If you have 64-bit Office installed then you need to go into the project properties and update the debug command line arguments to "MyFirstRibbon-AddIn64.xll" as outlined in the previous post.

The project is ready now so we can start coding by adding the XML which defines the custom tab and its button. Select the MyFirstRibbon-AddIn.dna file in the solution explorer window and insert the following XML code inside the DnaLibrary tags:


<CustomUI>
 <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui'>
  <ribbon>
   <tabs>
    <tab id='myFirstTab' label='My First Tab' visible='true'>
     <group id='myFirstGroup' label='My First Group' visible='true'>
      <button id='myFirstButton' label='My First Button' size='large' imageMso='HappyFace' onAction='SayHello' visible='true'/>
     </group >
    </tab>
   </tabs>
  </ribbon>
 </customUI>
</CustomUI>

The dna file should now look like this:

1stribbondna

If you’ve customised the ribbon in your VBA projects then the above XML should look very familiar to you except for the outer CustomUI tags (with a capital "C") which are idiosyncratic to Excel-DNA.

The dna file is the default place Excel-DNA checks for custom ribbon XML. You can store the custom XML elsewhere but, to do so, you have to override the MyRibbon.GetCustomUI() method. Overriding is a more advanced topic and something to be discussed another day.

Now we need to add the code for the myFirstButton button’s onAction callback. Since we want to show a message box when the button is clicked, we need to add a reference to System.Windows.Forms to our project:

  • Project > Add Reference > Assemblies > Framework
  • Tick System.Windows.Forms and click OK.

1stribbonreference

Select the Class1.cs file in the solution explorer and add the following using statements to the top of the module:

using System.Runtime.InteropServices;
using System.Windows.Forms;
using ExcelDna.Integration.CustomUI;

And the following class inside the MyFirstRibbon namespace:

    [ComVisible(true)]
    public class MyRibbon : ExcelRibbon
    {
        public void SayHello(IRibbonControl control1)
        {
            MessageBox.Show("Hello!");
        }
    }

The Class1.cs module should now look like this:

1stribbonclass1cs

The project is now ready for debugging. When you click on the debug button, Excel should launch and you should see the “My First Tab” on the ribbon. When you click on the My First Button you should get a message box:

1stribbonhelloexcel

I really like this. In particular, having a single IDE for both the XML and the callbacks is something which is severely lacking in the VBA development world, so doing it this way makes quite a refreshing change!

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Excel-DNA, Microsoft Excel, Visual C# and tagged , , . Bookmark the permalink.

3 Responses to My First Custom Ribbon Using Excel-DNA And Visual Studio

  1. David Reddy says:

    Great post. Very clear. Thanks Colin.

    Like

  2. Great, have you tried creating a dynamic ribbon which ‘turns-on / turns-ff’ buttons and or tabs depending on which workbook is opened or selected?

    Like

  3. Pingback: #Excel Super Links #12 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s