[ou81aswell]

I read Customizing the Excel 2007 Ribbon using VBA with great interest.

Does anyone know how to do this for a VB6 based Excel COM Add-In?

Is there a change one can make to the existing designer code to implement the extra interface required by the Ribbon?

[Mike Rosenblum]

Hi ou812,

Welcome to the Forum

The Ribbon is actually designed to be accessed via Managed COM Addins, that is to say, .NET DLL's which are exposed to COM. Since Managed COM Addins are really just "emulating" a true VB 6.0 DLL Addin, then, yes, a VB 6.0 Addin could use the same interfaces, because they are, in fact, COM interfacses. (IRibbonControl is the main interface involved.)

That said, I've never tried to do this and so, well, you'll have to do some reading on your own...

I would start by taking a look at Post #2 of the tutorial, where you'll see some relevant links, particularly this one: Customizing the Office (2007) Ribbon UI for Developers (Part 1 of 2). This is written from the point of view af a .NET Managed COM Addin, but I *think* you should be able to work it out. (If not, come back here, ask where you get stuck and I'll give it a shot.)

Let us know how it goes...
Mike

[ou81aswell]

I have been able to implement support for IRibbonExtensibility and IRibbonControl in a VB6 based COM Addin for Excel.

First I had to create a type library that defines the interfaces for IRibbonExtensibility and IRibbonControl. Because I didn't want to include the Office 12 library in my references (I reference the Office 2000 lib so that the addin works in 2000, 2002, 2003 and 2007) I used Ole Viewer to look at the Office 12 typelib (C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL) for the above mentioned interfaces and then simply did a copy/paste to a text editor. I then used GUIDGEN to produce a UUID GUID for the type library. Finaly I used the MIDL command line utility with the /tlb switch to produce the resulting tlb. I have attached a ZIP file containing the IDL file and resulting tlb file, which you should be able to reference in a VB6 project.

Next, I added a reference to the tlb file in the project and added the following code to my designer class:

Implements IRibbonExtensibility

Private Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String

IRibbonExtensibility_GetCustomUI = LoadTextFile(PathAppend(App.path, "ribbon.xml"))

End Function

Public Sub RibbonHandler(ByVal Ctrl As IRibbonControl)

' code to handle control clicks
' Ctrl.Id and Ctrl.Tag propertiers can be used to discover the source of the activity

End Sub

As you can see, I used a function called LoadTextFile to load the contents of my ribbon customizations from an xml file.

The onAction tags in my XML ribbon markup point to the public sub RibbonHandler. I also make use of the id and tag values of the control in the RibbonHandler code to determine what control was clicked. This way I need only one handler.

<button id="customButton1" tag="MyParameter" label="ConBold" size="large" onAction="RibbonHandler" imageMso="Bold" />

BTW, I noticed that if there is even the slightest problem with the ribbon markup, the customizations to the ribbon will not show up.

Anyway, I hope this helps.

BTW, the type library will appear in the list of references as jmwIRibbonExtensibility

[Mike Rosenblum]

Very impressive...

I'm not sure why you wanted to avoid the Office 12 library, as the Ribbon only exists for Office 12 at this point, but that was a great description of how to do it.

This will probably come in handy at some point. Thanks a ton, and I'm sure other Excel guys will apprciate it as well.

,
Mike

[DennisW]

Very nice - Thank you

[ou81aswell]

Customizing the Office 2007 Ribbon UI - Part 5 also shows how to do this with VB6.

I find it very usefull to have the "Show add-in user-interface errors" checked in Office (That big round button at top left) -> Application Options -> Advanced ... General

With this item checked you get an explanation as to why your ribbon customization is failing.

[svercek]

This is absolutely GREAT!! I can compile my Outlook Addin using OL2000 reference and the Addin works using the Ribbon in OL2007.

I have one problem, I need to be able to declare a variable as Office.IRibbonUI. This does not seem to work. I tried to open the Office 12 MSO.DLL in the OLE Viewer but it fails to open so I cannot get the IRibbonUI definition. What OLE Viewer are you using? Or, if you have an update to the file please post it.

Thanks
John

[DennisW]

Hi,

I need to be able to declare a variable as Office.IRibbonUI

One way is to use late binding although it defeat my opinion of always using early binding. However, one approach would be to check which version is in use and based on that declare the variable.

The Office 12 MSO.dll is about 15.000 lines and I've been trying to extend the suggested solution so I can make callbacks etc. However, so far I've been unsuccessfully.

[svercek]

Hmmmm, should have thought of that myself. However, it still fails during execution on 2007 with the following message -
An exception occurred while calling function "Ribbon_OnLoad". The exception
message is: ActiveX component can't create object.

Here is the code snippet that works in 2007 but does not compile in 2000

*************************************************
Public m_Ribbon As Office.IRibbonUI

'create a reference to the ribbon when it is created
'so it can be reloaded if registration changes
Public Sub Ribbon_OnLoad(ByVal Ribbon As Office.IRibbonUI)
Set m_Ribbon = Ribbon
End Sub


Here is what I tried that compiles in 2000 but fails in 2007 with the above message

******************************************
Public m_Ribbon As Object

'create a reference to the ribbon when it is created
'so it can be reloaded if registration changes
Public Sub Ribbon_OnLoad(ByVal Ribbon As Object)
Set m_Ribbon = CreateObject("Office.IRibbonUI")
Set m_Ribbon = Ribbon
End Sub


If I remove the CreateObject statement then the m_Ribbon.InvalidateControl does not reload the ribbon.

Any thoughts on what might work?

[svercek]

I found the problem with the CreateObject. It turns out you cannot do late binding on the IRibbonUI according. See the following link:

http://www.microsoft.com/communities...xp=&sloc=en-us

It appears the user may have to restart Outlook to reload the Ribbon.

[Mike Rosenblum]

Well, yes, but this does not really have to do with the Ribbon per se. Since Outlook is a single-instance application, your addin will not run immediately if installed/registered while Outlook is already open. Upon opening the next time, Outlook will check the registry and/or the folder locations for VBA Addins and load as appropriate. (You could probably force your addin to load immediately by using Automation, if you really wanted to force the issue.)

Overall, since you are using Outlook here, you would probably want to give this a read:

Customizing the Ribbon in Outlook 2007 (MSDN2 2007)

Ok, the next issue is that you are trying to use the following signature using the Ribbon_OnLoad() call-back:

Public Sub Ribbon_OnLoad(ByVal Ribbon As Object)

While the expected call-back signature is:

Public Sub Ribbon_OnLoad(ByVal Ribbon As Office.IRibbonUI)

Technically, what you are attempting here is called "contravariance", where the parameter type is wider (weaker-typed) than the argument type being passed in. That is, your handler accepts any 'Object' (btw, using 'As Variant' would have been even wider) and the caller is passing in a 'Office.IRibbonUI' object. So *in theory* this could work...

The problem is whether the calling convention allows for this. There is nothing in the rules to prevent it, but most scenarios like this do not allow for contravariance currently. For example, while C# can do this with delegate callbacks, VB.NET cannot. I don't think that VB 6.0 and VBA allow for contravariant event handling or callbacks enabled by the language or runtime itself.

This approach could still work in VB6 or VBA if the caller (Excel in this case) when calling via late binding is willing to call a method with the correct name and "acceptible signature" as opposed to looking for an "exact signature". My guess is, however, that it is looking for an exact match.

You could *try* it, but if you do, avoid the 'CreateObject("Office.IRibbonUI")' approach you tried before and instead add a MsgBox so that you can see if the call-back is actually occurring. Something like this:

Public m_Ribbon As Object

Public Sub Ribbon_OnLoad(ByVal Ribbon As Object)
    MsgBox "Ribbon_OnLoad() called!"
    Set m_Ribbon = Ribbon
End Sub

I'm guessing that it won't get called, but you would have to try to find out for sure.

From a practical standpoint, I think that it might be best to just compile two different versions, one for Office 2007 and the other for Office 2003 and below. If you wish to have "all your code" in one project, then you could still do so by using conditional compilation constants (#IF, etc.), or make a library with code that is common to both versions and then write custom front ends, one for Office 2003 and below and the other for Office 2007.

Just my 2c anyway...

Mike

[svercek]

Mike, the issue isn't about loading the addin without a restart of Outlook. It is about a button on my tab that, once pressed, should disappear. Once the ribbon is loaded you cannot change it without reloading it (or at least the control you want to change). The invalidatecontrol("controlname") command of the ribbon reloads that button. Prior to the reload I set the visible property of the control to False so it no longer appears on the ribbon.

I need a reference to the IRibbonUI object to do this.

Note in my previous post that I did try removing the createobject line and the onload code runs. However, when I get to the point of doing the InvalidateControl it fails.

I have found another tlb by XL-Dennis that contains the definition for the IRibbonUI. I have not tried that yet but it is supposed to work.

Thanks
John

[Mike Rosenblum]

Originally Posted by svercek

Mike, the issue isn't about loading the addin without a restart of Outlook. It is about a button on my tab that, once pressed, should disappear. Once the ribbon is loaded you cannot change it without reloading it (or at least the control you want to change). The invalidatecontrol("controlname") command of the ribbon reloads that button. Prior to the reload I set the visible property of the control to False so it no longer appears on the ribbon.

I need a reference to the IRibbonUI object to do this.

Yes.

Note in my previous post that I did try removing the createobject line and the onload code runs. However, when I get to the point of doing the InvalidateControl it fails.

Ok, right, you said that - my bad. On the other hand, that it runs at all is actually vere good news...

Just a guess, but, since you are using late-binding, the variable holding the Office.IRibbonUI interface object is therefore typed 'As Object' and not 'As Office.IRibbonUI'. We can late bind against an interface generally only if the class's default interface implements the method explicitly. That is, only if the control itself has a public 'InvalidateControl()' method. I don't know for sure, but I guess since your call is failing, that this is the reason. On the other hand, if you are not getting a run-time error from the call to InvalidateControl() one would *think* that this worked, hmm....

In order to late-bind against the interface, you have to, well, bind to the interface -- we're comming full circle here. I'm guessing that there is a low-level approach that using the GUID for the IRibbonExtensibility interface and a call to IUnknown.Queryinterface, and then, well, you need to bind to IRibbonExtensibility in order to have the right offsets to the implemented method. (I think the only way to avoid binding to IRibbonExtensibility would be a low level hack where you know the numerical offsets beforehand and simply call a function at the correct offset from address after getting the location of the IRibbonExtensibility interface via IUnknown.Queryinterface along with the GUID. There are APIs that can probably achive this, but this approach doesn't sound smart...)

I have found another tlb by XL-Dennis that contains the definition for the IRibbonUI. I have not tried that yet but it is supposed to work.

Yes, this would be the way to go... Although, Dennis' comments above give pause. I guess, then, he could not get this to *quite* work? I'm not sure...

[Flowchart Dude]

Great thread. This clears up a number of things that weren't obvious to me after reading XLDennis' blog several months ago.

BTW, if anyone wants another good resource, the RibbonX chapter of "Excel 2007 VBA Programmer's Reference" is available as a free download [1]. It's gives a detailed overview of the Ribbon. (3 of the 4 authors are Bullen, Bovey and Green - aka the "Professional Excel Development" guys. Good stuff.) Also, Bullen has posted LoadPictureGDI, a module to load png's for custom icons [2].

[1] http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm
[2] http://www.bmsltd.ie/Excel/SBXLPage.asp

[Mike Rosenblum]

Thanks for those links Flowchart Dude!

For those reading along, the "XL-Dennis" blog post to which people are referring can be found here:

Creating a standalone type library for IRibbonExtensibility

-- Mike

[Marcel502]

Hello Mike,

I only just want to hide the ribbon from my VBA-code.
Is this not possible without using xml?

thank you,
Marcel

[Mike Rosenblum]

Yes, you need to use XML, which you woul include as part of your workbook or XLA addin.

Basically, you would use

<ribbon startFromScratch="True">

but a few items in the 'File' menu will still exist and you'll have to remove those explicitly with a little more XML...

To get started, you should have a look at the Customizing the Excel 2007 Ribbon using VBA tutorial. It covers the basics for getting started quickly using Ribbon XML within a VBA project and has a lot of links to other important Ribbon XML tools and tutorials.

Hope this helps...
Mike