Custom Ribbon Cancels CommandBar Load

Nov 28, 2011 at 4:56 PM
Edited Nov 28, 2011 at 4:58 PM

Hi Guys,

I'm running ExcelDNA version 0.29 and I've noticed an issue when trying to create custom context menu items and a ribbon for my excel addin. (I am running Excel 2007).

Briefly, the issue is as follows. I have a dna file like this:

<DnaLibrary RuntimeVersion="v4.0" Name="XYZ Excel Addin">
  <ExternalLibrary Path="XYZAddin.dll" LoadFromBytes="false" ExplicitExports="true"/>

  <CustomUI>
    <commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars' >
      <commandBar name='Cell'>
        <button before='1' caption='New context item' enabled='true'  ShortcutText='CTRL+A' onAction="ShowHelloMessage"/>
      </commandBar>
    </commandBars>
  </CustomUI>

  <CustomUI>
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="RibbonLoad">
      <ribbon>
        <tabs>
             <!-- ETC,ETC -->
       </tabs>
      </ribbon>
    </customUI>
  </CustomUI>
</DnaLibrary>

Then I have a custom Ribbon class that implements the ExcelRibbon interface:

    [ComVisible(true)]
    public class XYZRibbon : ExcelRibbon{
    }

I noticed that when the custom ribbon class is detected, the context menu never shows up. I debugged through the code and see the line responsible for this behavior in DnaLibrary.cs line 322:

foreach (AssemblyLoader.ExcelAddInInfo addIn in _addIns)
{
    if (addIn.IsCustomUI)
    {
        // Load ExcelRibbon classes
        ExcelRibbon excelRibbon = addIn.Instance as ExcelRibbon;
        excelRibbon.DnaLibrary = addIn.ParentDnaLibrary;
        ExcelComAddInHelper.LoadComAddIn(excelRibbon);
        uiLoaded = true;
    }
}

// should we load CommandBars?
if (uiLoaded == false && CustomUIs != null)
{
foreach (XmlNode xmlCustomUI in CustomUIs)
{
    if (xmlCustomUI.LocalName == "commandBars")
    {
        ExcelCommandBarUtil.LoadCommandBars(xmlCustomUI, this.GetImage);
    }
}
}

Based on the above code, the uiLoaded flag triggers due to my XYZRibbon class, and then the command bars never load (which means my context menu is nowhere to be found).

If I manually load the command bars in my IExcelAddIn's AutoOpen method, it works fine:

private const string ContextPopups =
    @"
    <commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars' >
        <commandBar name='Cell'>
        <button before='1' caption='New context item' enabled='true'  ShortcutText='CTRL+A' onAction='ShowHelloMessage'/>
        </commandBar>
    </commandBars>";

public void AutoOpen()
{
        ExcelCommandBarUtil.LoadCommandBars(ContextPopups, null);

}

So my question is: Is this behavior by design? Is there any issue I should be aware of when loading both a ribbon and a context menu? I see the commment "// should we load CommandBars?" before the code that loads them, so I was wondering whether doing this will create some side effect that isn't readily observable. (On the other hand, if this is a bug, I hope this helps to address it!)

Thanks!

Ismail Degani

Coordinator
Nov 28, 2011 at 9:39 PM

Hi Ismail,

I agree with everything you describe - thanks for the great write-up!

Your workaround is fine and should not have any side effects.

The ribbon/command bar implementation works 'as designed', but I only realised afterwards the issue of using the ribbon on Excel 2007 and also having a CommandBar-based context menu. This is complicated even more by the fact that Excel 2010 uses the ribbon to also customize the context menus. I have not tried to fix this before now.

My first thought for a fix in the next version is as follows:

* If running on Excel 2010 and there is an Excel 2010 ribbon in the .dna, load that and nothing else.

* Else If (running on Excel 2007 or Excel 2010) and there is an Excel 2007 ribbon in the dna, load that. In addition,  there is a CommandBar with "name='Cell', load that CommandBar and nothing else.

* Else if running on Excel < 2007, load any CommandBars.

Does that seem about right?

Do we have to worry about other context menus, like right-click on a chart? Do you know the name for these other command bars??

Regards,

Govert

 

Nov 30, 2011 at 4:21 PM

Hi Govert, thanks for the speedy response! Your solution is great, exactly what I had in mind. It's a shame the Excel API is so inconsistent from version to version, but I think that should address the vast majority of use-cases.

Unfortunately I don't know all of the command bars that a user might want to include. Maybe add an attribute "ForceLoad" in the CustomUI tag so if a user *really* wants to load a commandbar other than "Cell", they can still do so?