Getting selection from Ribbon vs Menu

Jun 26, 2014 at 2:46 PM
Edited Jun 26, 2014 at 4:44 PM
Hi Govert/everyone,

I have an ExcelCommand that I run from the menu and/or by keyboard shortcut that calculates the current selection:
[ComVisible(true)]
public partial class XL : /*ExcelRibbon,*/ IExcelAddIn
{
    [ExcelCommand(MenuName = "&Zeus", MenuText = "Calculate Selected &Range", ShortCut = "^+R")]
    public static void CalculateRange()
    {
        dynamic app = ExcelDnaUtil.Application;
        app.Selection.Calculate();
    }
 }
I'd like to be able to do the same thing by hitting a button on my Ribbon too, but this fails:
[ComVisible(true)]
public class ZeusRibbon : ExcelRibbon
{
    public void RunCalculateRange(IRibbonControl control)
    {
        XL.CalculateRange();
    }
 }
Does anyone know how I can fix this?

Thanks.
Coordinator
Jun 26, 2014 at 2:58 PM
What do you mean by 'it fails'? Is the ribbon callback ever called?

What does your ribbon markup look like?

There are two options for hooking up a callback for a ribbon button:

Option 1: Set the onAction as 'RunTagMacro' and put a registered (static/ExcelCommand) macro in the tag:
          <button id='Button1' label='...' onAction='RunTagMacro' tag='CalculateRange' />
RunTagMacro is refined in ExcelRibbon, and just calls Application.Run(theTag).

Option 2: Set up an onAction callback on the ribbon class
          <button id='Button2' label='...' onAction='CalculateRange'/>
Both of these options should work in your case.

-Govert
Jun 26, 2014 at 6:25 PM
Edited Jun 26, 2014 at 6:48 PM
Here's the markup:

<button id='Button2' label='Calculate Range' imageMso='CalculateNow' size='large' onAction='RunCalculateRange' tag='Calculate Selected Range' />

I've debugged through and I can't reproduce that problem -- I wonder if it's a corner case that only crops up occasionally.

Having said that, here's another function that I'm trying to do the exact same thing with.

Ribbon function:
    public void RunTidyArray(IRibbonControl control)
    {
        XL.TidyArray();
    }
Markup:
          <button id='Button1' label='Tidy Array' imageMso='DatasheetNewField' size='large' onAction='RunTidyArray' tag='Tidy Array Formula' />
Menu command that the ribbon function calls:
    [ExcelCommand(MenuName = "&Zeus", MenuText = "&Tidy Array", ShortCut = "^+T")]
    public static void TidyArray()
    {
        ExcelRange selection = new ExcelRange(XlCall.Excel(XlCall.xlfSelection, new object[0]) as ExcelReference);
        if (selection.HasFormula)
        {
            string formula = selection.Formula;
            //string newFormula = Regex.Replace(formula, "([A-Z]{1,2}[0-9]+)", "!$1");
            string newFormula = Regex.Replace(formula, @"(\${0,1}[A-Z]{1,2}\${0,1}[0-9]+)", "!$1");
            newFormula = Regex.Replace(newFormula, "!!", "!");

            object value = XlCall.Excel(XlCall.xlfEvaluate, newFormula);
            if (value is ExcelDna.Integration.ExcelError || (((object[,])value)[0, 0] is ExcelDna.Integration.ExcelError))
                value = XlCall.Excel(XlCall.xlfEvaluate, formula);

            if (value is object[,])
            {
                object[,] values = value as object[,];
                int nRows = values.GetLength(0), nCols = values.GetLength(1);

                ExcelReference newRef = new ExcelReference(selection.AsRef.RowFirst, selection.AsRef.RowFirst + nRows - 1,
                                                           selection.AsRef.ColumnFirst, selection.AsRef.ColumnFirst + nCols - 1);
                XlCall.Excel(XlCall.xlcSelect, newRef);
            }
        }
    }
This function works fine if I invoke it using the menu or by keyboard shortcut. However, when I run it through the Ribbon, the following exception is thrown on the line shown:
    "Exception of type 'ExcelDna.Integration.XlCallException' was thrown."
EDITED: To include the full body of the function.
Coordinator
Jun 26, 2014 at 7:06 PM
Edited Jun 26, 2014 at 7:59 PM
XlCall.Excel makes calls via the Excel C API. This is not supported in the Ribbon callback context.

You can either use the COM object model (as in your initial post) or transition to a macro context where the C API is supported.

To transition to a macro context you can:
  1. Follow the onAction='RunTagMacro' tag='TidyArray' approach, where the ExcelCommand name goes in the tag.
  2. Switch to the macro context yourself, by calling ExcelAsyncUtil.QueueMacro("TidyArray") or ExcelAsyncUtil.QueueAsMacro(() => TidyArray());.
-Govert
Jun 26, 2014 at 7:37 PM
Thank you for that - I tried method 1 but it doesn't seem to have done anything. Here's what I've got now:

Markup:
          <button id='Button1' label='Tidy Array' imageMso='DatasheetNewField' size='large' onAction='RunTidyArray' tag='TidyArray' />
Ribbon function:
    public void RunTidyArray(IRibbonControl control)
    {
        XL.TidyArray();
    }
And macro function:
    [ExcelCommand(MenuName = "&Zeus", MenuText = "&Tidy Array", ShortCut = "^+T", Name = "TidyArray")]
    public static void TidyArray()
    {
        ExcelRange selection = new ExcelRange(XlCall.Excel(XlCall.xlfSelection, new object[0]) as ExcelReference);

        ...

    }
Coordinator
Jun 26, 2014 at 8:02 PM
Edited Jun 26, 2014 at 8:03 PM
For option 1. the markup should read onAction='RunTagMacro' tag='TidyArray'. Then the ExcelRibbon.RunTagMacro callback will be called instead of your own callback (so you can delete your own ZeusRibbon.RunTidyArray method). The ExcelRibbon.RunTagMacro(...) implementation looks at the control's tag property, and calls Application.Run(...) with the value.

-Govert
Jun 26, 2014 at 9:31 PM
Oh, I see - I didn't realise 'RunTagMacro' was a callback! Thank you.