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:

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:

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.

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:

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:

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!

Advertisement