Debugging RibbonX Invalidate/InvalidateControl failures…

I ran into this the other day when I was working on a file and it drove me nuts.

Background:

I always use a custom property to contain my RibbonUI object. It's set up like this:

The following code goes in the ThisWorkbook class module:

Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
'Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
'Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property

And then the onLoad statement, placed in a standard module, looks like this:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub

At that point I can invalidate the entire ribbon by using:

ThisWorkbook.ribbonUI.Invalidate

Or just a single control:

ThisWorkbook.ribbonUI.InvalidateControl "ControlName"

The Issue:

This particular problem started for me when my InvalidateControl methods weren't working. This was irritating, but I've debugged a lot of RibbonX before, so I thought it may be the control name. After checking the XML I was pretty sure it wasn't, but just for grins I switched to try to Invalidate the entire ribbon. That didn't work either…

Next up on the debugging mission was to step through the code, at which point I discovered that I had no reference to the RibbonUI object. As you've no doubt figured out if you worked with the ribbon, you can't invalidate your controls without a RibbonUI object to work with. So somewhere I thought I must be triggering an error which caused the RibbonUI to lose scope. Despite a ton of searching through the VBA though, I couldn't find anything that would trigger this kind of error.

Given that my code all looked good, I added a single line into my OnLoad statement so that it read as follows:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
Debug.Print "Fired!"
ThisWorkbook.ribbonUI = ribbon
End Sub

I then opened the file again, and found no message in the immediate window. Hmmm… The callback was obviously never called at all. This is a little strange, as this callback should be triggered as soon as the file was opened. So then I checked the remaining things that could cause these kind of issues:

  • I checked the Trust Center to ensure that:
    • macros were set to "Disable With Notification"
    • The folder where the file was stored (on my network) was a Trusted Location (and that subfolders were also trusted)
  • I checked that the onLoad callback matched that generated by the customUI Editor
  • I check that UI errors would be displayed (just in case my XML wasn't valid). You can find this under OfficeàExcel OptionsàAdvanced near the bottom of the list:

None of these earned me any joy though. Things still didn't work. I then emailed the files to a friend and guess what… they worked!

With the problem isolated to my machine, I then disabled all my other add-ins and opened Excel fresh. Lo and behold, it worked!

The Fix:

When we wrote the RibbonX book, Robert and I standardized on naming the onLoad callback rxIRibbonUI_OnLoad. I'm starting to rethink that advice a bit…

In my case, I had two add-ins with this same onLoad name, one of which had the onLoad callback declared as a Public routine, rather than private. This caused a conflict when I wrote a new add-in and used the exact same procedure/callback name. Just to make this clear:

Add-in #1 used:    Public Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Add-in #2 used:    Private Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Even though Add-in #2's onLoad was correctly written, at load time it did not run. I didn't actually test this to be totally sure, but I'm 99.9% confident that Add-in #1's onLoad DID fire instead.

So some suggested rules for you if you're building an add-in:

  • Code to avoid conflicts. Even if you are only writing one add-in, you never know if you'll end up installing one from somewhere else. If both you and the other author followed our naming convention, and the other author screwed up their callback by declaring it as Public, YOUR add-in will not run correctly. (Nice, eh?)
  • Declare (ALL) your RibbonX callbacks as Private. This will avoid pushing this issue on someone else. By default, the customUI editor authors its callbacks as Sub blahblah(). Omitting the Private keyword by default leaves it as Public.
  • Forget about Option Private Module. If you think you can use Public callbacks and just declare the module private with "Option Private Module", see rule #2. (Trust me. That one I did test, and is what led to this whole mess.)
  • Add a project specific name to the end of the callback. E.g. "rxIRibbonUI_onLoad_FWBudgetTools". While I still believe in standards, you need to tag it with differentiating text to avoid conflicts with other add-ins. Oh sure, it may look long and unwieldy but let's face it -- you're only going to write this routine once per project and forget all about it after that!


12 thoughts on “Debugging RibbonX Invalidate/InvalidateControl failures…

  1. This is a particuarly nasty problem Ken in that something just doesn't work, but this is not a new issue, nor ribbon specific. I recall that when Frank Kabel and I wrote QDE a call to the help invoked some other help file. The problem was that we called the procedure ShowHelp as did the other addin (I am pretty sure it was one of Rob Bovey's, I recall writing to him to check that he used that name). We were lucky becuse we got a very visual response, even if odd. Since then I have always used the App Id in the procedure name for the help invocation. I will just take your advice and use it in your callbacks as well.

  2. Looks like that might be a very good idea, Bob. I just found out today that this issue is even bigger than the onLoad routine... it's any conflicting add-in. Not good.

  3. Pingback: The Ken Puls Blog » Blog Archive » Ribbon Example Table of Contents

  4. Pingback: The Ken Puls Blog » Blog Archive » Demonstration of RibbonX add-in conflict dangers

  5. A similar issue related to this one is the following question:

    Is there a way to declare the Ribbon UI object as a constant in your VBA rather than using the onLoad routine and a variable?

    I want to know this because if the code debugs or if the user resets the VBA project then the reference to the UI object is lost so any Invalidate routines produce a runtime error. This can be quite frustrating, as the only solution (as far as I know?) is to reload the workbook.

    Thanks

  6. Hi Will,

    Unfortunately, no. It's a very poor implementation, imo, in that there is nothing we can do to keep the RibbonX object alive. One crash and you're back to reload to reinstitute it. 🙁

  7. Hi,

    I have a small question regarding the order of execution when a workbook is loaded.

    I find that the initializing callbacks are made only when the custom tab generated by the workbook is clicked. But if StartFromScratch is set true in the xml code, then they are made as soon as the workbook is loaded.

    Which brings up the question. Is it guaranteed that whatever VBA code is used to initialize those callback variables will execute _before_ the initial callbacks are made?

    Can we just put that code in Workbook_Open in ThisWorkbook and safely assume that those variables will be set by the time the initial callbacks are made?

  8. Hi Will,

    Yes, this was a technique that was worked out only a few months ago. But that is exactly what it is intended to do. 🙂

  9. thanks a lot
    i've got the same problem
    i've developed many excel addins in xlam format but i don't ussually activate any of them at the same time
    i change the id of the addins that causes the conflict and the problem was solved

  10. Hello Ken,
    It's been a while since the last post, but I'll give it a try...
    I can't get my ribbon code working in PowerPoint when I declare the OnLoad callback as Private. When I open my presentation I get the message: macro not found. It only works when I declare the callback Public. Any idea how to get it working? Joost

  11. Honestly, that sounds a bit odd, but I guess the question is... if it's working with it declared as Public, do you care?

Leave a Reply

Your email address will not be published. Required fields are marked *