How to create a custom ribbon Addin for Excel 2010

Creating a custom ribbon addin for Excel can be done in a few steps

1. Develop your VBA code in a new Excel sheet, save it as Excel addin .xlam
2. Open the addin with Custom UI Editor to create Buttons, Groups, etc. and link it with your VBA code
3. Add the addin directory to the secure folders in excel
4. Configure Excel to load your addin at startup

Here are the required steps in detail

Start Excel and develop your VBA code as you did before. If you do not see a development tab in excel, activate it via the options menu. File > Options > Ribbon > On the right side check “Development Tools”

You need to create an event handler to make your functions callable from the UI. Here is an example for a function and an event handler. The event handler foo_eventhandler calls the function foo which displays a message box with “Hello World from Excel”.

Sub foo_eventhandler(control As IRibbonControl)
    foo
End Sub

Sub foo()
    MsgBox ("Hello World from Excel")
End Sub

Save your Excel as Excel Addin (.xlam) to your C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins directory. You can save it wherever you want, but the predefined addins directory might be good idea.

Yet the addin does not have any UI elements. You have to define these elements by yourself. Download and install the Custom UI Editor from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx . Start the editor and open your previous saved .xlam file. The editor shows a blank text field. From the Menu > Insert > Sample XML choose Excel Custom Tab. Use the onAction property at the button to call an eventhandler. Here is an example code to call the foo_eventhandler

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="MyTab" insertAfterMso="TabHome">
                <group id="customGroup" label="MyGroup">
                    <button id="customButton1" label="Say Hello" size="large"
                     onAction="foo_eventhandler" imageMso="HappyFace" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Start Excel from the program menu. Go to File > Options > Security Center > Settings > Secure Locations > Add Location, and add your addins directory e.g. C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins as secure directory to load stuff from. Close Excel.

When you open the .xlam file using Excel, you can already use your addin. However, it is not available when you start Excel from the programs menu. To load the addin when excel starts, go to the development tab  > Add-Ins and check your addin. (The form displays addins from the Appdata\Roaming\Microsoft\Addins directory)

Now the Addin is loaded when Excel starts. Whenever you change something and the Addin does not appear anymore, go and check your xml UI code. Make sure that all IDs are unique and do not have blanks
(e.g. id=”My Button2” is a bad idea)

More Icons

There are many icons available you can use for your addins. For example download the icon gallery addin from http://www.accessribbon.de/index.php?Downloads:24 . To change the icon on button modify the imageMso property in your xml file.

<button id="customButton1" label="Say Hello" size="large"
 onAction="foo_eventhandler" imageMso="HappyFace" />

Have fun!