Customize Ribbon UI for Excel 2007 with ‘Managed’ COM Add-ins (VB.NET 2005)

Customize Ribbon UI for Excel 2007 with ‘Managed’ COM Add-ins (VB.NET 2005)

For a version that cover COM Add-ins developed with classic VB please see the following post: Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

The Customized RibbonX ‘Manifest’
The NotesRibbon.XML file is embedded in the project and the only difference between this file and the one used in the previously post (as above) is the following:

<button id=“hlp”
screentip=“Help with Notes Tool.”
onAction=“NotesButton” />

Code in the Connection class
In this case I use Windows icon files and therefore they need to be converted to bitmaps before we can retrieve the IPictureDisp for them. The function GetResourceBitMap below takes care of the converting process. In order to retrieve the IPictureDisp for the bitmaps we need to get access to the protected method GetIPictureDispFromPicture in the class System.Windows.Forms.AxHost. For the case it’s solved by using a separately class which is presented after the code for the Connection class.

All the Windows icon files (32×32) are embedded and only the relevant code for the case is showed here.

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core ‘To implement and use the IRibbonExtensibility.
Imports System.Windows.Forms ‘To use MessageBox.
Imports Extensibility ‘To implement and use the IDTExtensibility2.
Imports System.Runtime.InteropServices ‘Defines the GuidAttribute and ProgIdAttribute.
Imports System.Reflection ‘In order to use the method GetExecutingAssembly.
Imports System.IO ‘In order to use a Stream and a Streamreader.
Imports stdole ‘To work with the IPictureDisp
Imports System.Drawing ‘To work with the images.

Public Class Connect
    ‘In order to customize the Ribbon UI the IRibbonExtensibility interface must be implemented.
   Implements Office.IRibbonExtensibility
    Implements Extensibility.IDTExtensibility2

    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
        g_xlApp = Nothing
    End Sub

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
        g_xlApp = CType(application, Excel.Application)
    End Sub

    Function GetCustomUI(ByVal ribbonID As String) As String Implements Office.IRibbonExtensibility.GetCustomUI
        ‘Since the NotesRibbon.xml is embedded we need to fully qualify the resource name:
       Return GetResourceXML(“Notes.NotesRibbon.xml”)
    End Function

    Public Function GetResourceXML(ByVal resourceName As String) As String
        ‘Set a reference to the assembly during runtime.
       Dim asm As Assembly = Assembly.GetExecutingAssembly()
        ‘Declare the textreader.
       Dim xmlDataReader As StreamReader = _
            New StreamReader(asm.GetManifestResourceStream(resourceName))
        With xmlDataReader
            ‘Read and return the XML file in a chunk.
           Return .ReadToEnd()
        End With
    End Function

    ‘The Callback function for the customized Ribbon Notes Tool.
   Public Sub NotesButton(ByVal control As Office.IRibbonControl)
            Select Case control.Id
                Case “wbk” : Show_Form(“wbk”)
                Case “wst” : Show_Form(“wst”)
                Case “hlp” : Process.Start(My.Application.Info.DirectoryPath & “Notes_Tool.chm”)
                Case “abt” : Show_Form(“abt”)
                Case “xld” : Process.Start(“”)
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.ToString, g_CONST_TITLE)
        End Try
    End Sub

    ‘The Callback function for the customized images.
   Public Function GetImage(ByVal control As Office.IRibbonControl) As IPictureDisp
        Dim pictureDisp As IPictureDisp
        pictureDisp = Nothing
        Select Case control.Id
            Case “wbk” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“workbooks.ico”))
            Case “wst” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“worksheet.ico”))
            Case “abt” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“info.ico”))
            Case “xld” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“xldennis.ico”))
        End Select
        Return pictureDisp
    End Function

    ‘Function to convert Windows Icons to Bitmaps.
   Public Function GetResourceBitmap(ByVal resourceName As String) As Bitmap
        ‘Set a reference to the assembly during runtime.
       Dim asm As Assembly = Assembly.GetExecutingAssembly()
        ‘Since the icon files are embedded we need to fully qualify the resource names, i e
       Dim stream As Stream = asm.GetManifestResourceStream(“Notes.” & resourceName)
        ‘Convert the Windows icons to GDI+ Bitmaps.
       Dim image As Bitmap = New Icon(stream).ToBitmap()
        Return image
    End Function
End Class

Code in the ImageAxHost class
By using a class, which inherits from System.Windows.Forms.Axhost, we get access to the protected method GetIPictureDispFromPicture. Below is the code in the ImageAxhost class:

Imports System.Windows.Forms
Imports stdole

Friend Class ImageAxHost
    ‘The class derives from System.Windows.AxHost.
   Inherits System.Windows.Forms.AxHost

    Sub New()
        ‘Intialize the base class to nothing.
    End Sub

    Public Shared Function Image_Convert(ByVal image As System.Drawing.Image) As IPictureDisp
        Return AxHost.GetIPictureDispFromPicture(image)
    End Function
End Class

The Output
The following image shows the output after that the code has been executed:


I find it quite easy to work with the Ribbon UI no matter if I use VBA, classic VB or VB.NET. However, I hope that MSFT will provide us with a better solution then the above when it comes to obtaining the IPictureDisp with VB.NET.

What is most notable is that when using ‘managed’ COM add-ins we can mix built-in images with customized which seems not to be the case when working with VBA or with COM Add-ins developed with classic VB.
Edit: With the recent B2TR it works very well to mix customized images with built-in images.
In view of the fact that Office 2007 will be shipped with more built-in images then any previously version I don’t see any strong need to use customized images. In that way some extra code work is saved.

In the above case and in the previous classic COM Add-in’s case I’ve created a unique tab (Notes Tool). It looks great but if we assume that the end users actually will use 6-8 add-ins and each of them have their own unique tab then it will no longer look so great. In my opinion we should use tabs in a spare some way and instead make use of the general Add-ins tab. Of course, with this tab we need to consider how much space we actually should use for our add-in(s).

Kind regards,

Posted in Uncategorized

6 thoughts on “Customize Ribbon UI for Excel 2007 with ‘Managed’ COM Add-ins (VB.NET 2005)

  1. Hi Dennis,

    Very impressive, as usual from you…

    I’m curious about what you wrote here, however:

    “What is most notable is that when using ‘managed’ COM add-ins we can mix built-in images with customized which seems not to be the case when working with VBA or with COM Add-ins developed with classic VB.”

    Are you sure that this is right? What hapens if you attempt this from a COM Add-in? I would think that using a COM dll would be equivalent becaues IRibbonExtensibility is actually a COM interface and so it is really .NET which is being ported to the interface via the Interop, not the other way around… Not that I don’t trust you! But I was wondering what happens with COM in your experience?

    Great set of articles though. You and Tushar have really shed a lot of light…

  2. Mike,

    From a logical point of view You’re correct (and that’s why I also decided to single it out to get feedback on it).

    With B2 I’ve not been able to do it while with B2TR it seems to work ‘as expected’.

    I’ll update the articles asap however I need to re-write the VB.NET project as Excel 2007 B2TR refuse to acceept it as a valid add-in!

    The B2TR, so far, looks very impressive :)

    Kind regards,

  3. I m developing COM AddIn using VB. But still I m not getting success to assign Images using Image Tag. I used imageMSO. It is woorking fine But I want to asign custom Images. So Can u help me how can I do this?

  4. Hello everyone,

    I have tried the code above. but when i launch excel 2007, the exact xml file is returned by the function getcustomUI, but nothing happened. no additional ribbon is found in the application. but the add-in is charged. May someone help me?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.