How to Create and Deploy Custom Ribbon Entries in MS Office (including custom icons) and avoid some Pitfalls

Background

For a project in our company I wanted to create a Excel-macro-based user interface for some SQL Server-based database. The macro VBA-programming went quite well (due to some experience I already collected in this field), but I wanted to provide two buttons in the Excel 2010 ribbon to give the user the possibility to trigger macros to read/write from/to the database.
Many hints are available in the web, but I did not com across a receipe which provides the steps to create custom UIs in a concise form. Thus, and as I went into some traps, I want to share my experiences in this post.

Challenge

As described above, I wanted to provide two end-user buttons in the Excel ribbon as custom UI elements. To achieve this goal I had to problems to solve:

• How to get the buttons into the ribbon?
• How to deploy these custom buttons with my Excel in the most user-friendly way?

Custom Elements via Excel Options

Excel itself provides the means to create custom ribbon elements. There is a number of articles out there describing how to do this - for instance "How to Add Your Own Macros to Excel 2010" on changdoo.org. The process is quite simple and is easily achieved.

To deploy the customized ribbon to your customers, Excel offers the possibility to export the changed settings to an xml-file (called *.exportedUI) via the menu:

File --> Options --> "Customize Ribbon" menu entry --> "Import/Export" button:

As external reference see as well THIS microsoft article.

I do not have the administrator means to distribute these buttons centrally but the so created configuration file can be sent to those users who want to incorporate the new buttons into their work environment.

Nevertheless this process has some shortcomings:

• The users have to follow a procedure to import the files with my buttons into their Excel environment 
• One has to distribute a seperate file which has seperate versions
• It is not possible to use customs icons for the buttons

And last but not least the export procedure creates xml-code with some traps, which become obvious if one investigates the *.exportedUI xml-code with some editor. The most critical to me is that the macro being called by "onAction" is referenced by the absolute path of the excel file where the export is made from. Here an example of a custom UI created by Excel Options:

Freshly exported to *.exportedUI, the code comes without formatting (no line breaks) and most important note the absolute path in the xml:

<mso:cmd app="Excel" dt="1" />
<mso:customUI xmlns:x2="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:x1="TFCOfficeShim.Connect.14" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat>
<mso:sharedControls>
<mso:control idQ="mso:FileNewDefault" visible="false"/>
<mso:control idQ="mso:FileSave" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileSendAsAttachment" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Spelling" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Undo" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Redo" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:SortAscendingExcel" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:SortDescendingExcel" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileOpenRecentFile" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileNew" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileOpen" visible="true"/>
<mso:control idQ="mso:PrintPreviewAndPrint" visible="true" insertBeforeQ="mso:FilePrintQuick"/>
<mso:control idQ="mso:FileSaveAsPdfOrXps" visible="true" insertBeforeQ="mso:FilePrintQuick"/>
<mso:control idQ="mso:FilePrintQuick" visible="true"/>
<mso:control idQ="mso:FileClose" visible="true"/>
</mso:sharedControls>
</mso:qat>
<mso:tabs>
<mso:tab id="mso_c4.1568D5E4" label="My New Tab" insertAfterQ="x1:IDC_TEAM_TAB">
<mso:group id="mso_c5.1568D5E4" label="My New Group" autoScale="true">
<mso:button idQ="myButtonID" label="Call My Macro" imageMso="ListMacros" onAction="Q:\FolderName\MyMacroExcel.xlsm!MyMacro" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>
</mso:ribbon>
</mso:customUI>

PITFALL: If this is deployed to other users, the referenced macro will not be found if the Excel file is located in another location. To get around this trap you have to modify the code in *.exportedUI manually and remove the absolute path to the macro.

Custom Elements via Direct Ribbon Customization

With the above said, I was not very content with the "Excel Options"-option. After some searching I came across the "Custom UI Editor" option, which utilizes an external tool avaiable for example HERE.

With this little program one can modify the UI resources of an MS Office file directly without the need to export the changes to some external file. The UI is quite simple and offers the possibility to upload custom icons as images (any standard image format) and enter custom ribbon code directly in the Excel file:

 The resulting ribbon customization is stored inside the Excel file and is hence deployed directly with the file. The so created new ribbon entry is not visible in the "Options" dialog above.

The following code worked in MS Word 2010 and Excel 2010:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="myTab" label="MYLABEL">
<group id="testCaseTool" label="My Text">
<button id="ID1" label="Text 1" size="large" image="iconBitmap1" onAction="myMacro1" visible="true" />
<button id="ID2" label="Text 2" size="large" image="iconBitmap2" onAction="myMacro2" visible="true" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

It will create a new entry in to ribbon with two buttons calling two different macros. Add your icons and you're done!

PITFALL: Very critical is the fact that the macros referenced in the must have a special input parameter as described in the forum post "VBA error 'Wrong number of arguments or invalid property assignments' when running macro via custom button".

If one references routines created as VBA "Sub subname()", you will run into an error "Wrong number of arguments...". Hence you will have to modify your VBA code accordingly and create a new VBA sub with the right input parameter "Sub newsubname(control As IRibbonControl)".




This is how I was able to produce a satisfactory custom ribbon entry which I could deploy to my colleagues without any further hassle.

I hope you can get something out of this post for your own project - keep on geekin'

WolfiG