IRibbonControl & VSTO & ExcelDNA - getting access to the RibbonButton from within a ExcelDNA Ribbon UI / Custom Marshalling

[Michael]

Hi,

GUI:
-------
I am currently converting an existing VSTO project over to ExcelDNA.

In VSTO, I have control over the form controls (buttons, labels etc)
within the actions (object sender) via casting the sender object to a
button etc. This means I can modify the labels etc, buttons etc.

However, within ExcelDNA you get a callback with a IRibbonControl. I
tried to cast the IRibbonControl to a RibbonButton, but this cast
failed. I then tried using Marshal.... to investigate the interfaces
that IRibbonControl extends, but without much success.

Is there a particular method I can use to get access to the underlying
RibbonButton?

Functions & Custom Marshalling:
------------------------------------------------
It would be really good to be able to change the marshalling behaviour
of ExcelDNA via some kind of configuration. Two examples would be
useful.
a) I have a static function that returns an object array. Null values
in the object array appear as 0's in excel. I would like them to be
missing, without using the ExcelMissing type.
b) I have a static function that accepts an object array. However I
would prefer nulls to ExcelMissing. I understand that using
ExcelMissing is better design, but it would be extremely useful to
change the behaviour of the marshalling via a simple attribute.

regards
Michael

Ps Thanks in advance. This is a great project, and the best thing is
that the source is available which make tracking issues really easy.

[Govert van Drimmelen]

Hi Michael,

I'll split your two questions into two replies.

Functions & Custom Marshalling:
-------------------------------
In this thread I posted some discussion on these marshaling issues:
http://groups.google.com/group/exceldna/browse_thread/thread/f05054a703935fbe.

This discussion on the CodePlex site talks about optional and default
values, with some ideas for how a helper class to deal with optional
values could look: http://exceldna.codeplex.com/discussions/236897.

I agree that more flexible and customizable marshaling would be really
nice, and it is certainly something I plan to look at again. As you
can imagine, I am a bit cautious fiddling with the marshaling stuff.
Also, this part of the project has not been a high priority so far -
you can already add any marshaling you like by adding a line or two in
your function.

For your array results, you'd need a to return empty strings instead
of nulls (ExcelMissing and ExcelEmpty are not useful return types -
Excel maps these to the double value 0.) Excel has no notion of a
function returning no value, or an 'empty' value, though an empty
string looks a lot like nothing on the sheet.

For the input values, basically you are saying that the default value
for your array input parameter should be 'null'.
Using some helper class like in the CodePlex discussion, you'd have:

public static object[,] MyArrayFunction(object[,] inputArg)
{
// If inputArg is missing, apply default value (null)
object[,] input = OptionalHelper.Check(inputArg, null);

// ... do work on input array here
// - maybe call your 'real' function
object[,] result = MyRealFunction(input);

// Convert nulls in result array to empty strings.
return ArrayHelper.NullsToEmptyStrings(result);
}

I agree this kind of wrapper could be cleaned up by enhancing Excel-
DNA, but you'd probably be trading two function calls inside your
function for one or two attributes on your method.

Regards,
Govert


On Mar 20, 2:38 am, Michael <s...@tavworks.com> wrote:
> Hi,

[Govert van Drimmelen]

Hi Michael,

This is part two of my reply, about the Ribbon.

Ribbon GUI:
-----------
This thread has many Ribbon-related links:
http://groups.google.com/group/exceldna/browse_thread/thread/1db415c2700a650b/00edae83397682a1.

The RibbonButton class you refer to is a wrapper class defined in one
of the VSTO libraries. At the moment, Excel-DNA only supports the raw
Excel Ribbon interface - we have no such wrapper layer yet. This means
you have full access to the Ribbon from Excel-DNA, but need to deal
with the 'interesting' interface the Office developers chose to
expose.

For example, to be able to change the label of a button at runtime,
you need to:
* Add a method (say GetLabel) in your ExcelRibbon-derived class that
will return the label of a control.
* Register the GetLabel function in the ribbon xml as the label
handler: getLabel="GetLabel"
* Have some backing property (maybe in a class called MyExcelButton)
that remembers the last label value, which is returned by GetLabel
when the label is asked for.
* When you set the label property, call InvalidateControl("MyButton").
The ribbon will then call your GetLabel function to get the new label
value.

You'd need to do something like this for every property you'd want to
set at runtime.
(This is also what the VSTO wrapper classes do).

If you have a simple ribbon that is not too dynamic, doing this ad-hoc
is probably fine. But for a rich ribbon interface you'd have to make
some wrappers. Doing a wrapper for the whole Ribbon interface would
involve a lot of typing for a few days, but the ribbon is actually
well documented, and once you understand that there are no ribbon
'object' or functions you can call to modify the ribbon, the rest
should be quite straight-forward.

I'd be very happy to help anyone who wants to contribute a wrapper
like this to Excel-DNA.

Regards,
Govert


On Mar 20, 2:38 am, Michael <s...@tavworks.com> wrote:

> Hi,
>
> GUI:
> -------
> I am currently converting an existing VSTO project over to ExcelDNA.
>
> In VSTO, I have control over the form controls (buttons, labels etc)
> within the actions (object sender) via casting the sender object to a
> button etc. This means I can modify the labels etc, buttons etc.
>
> However, within ExcelDNA you get a callback with a IRibbonControl. I
> tried to cast the IRibbonControl to a RibbonButton, but this cast
> failed. I then tried using Marshal.... to investigate the interfaces
> that IRibbonControl extends, but without much success.
>
> Is there a particular method I can use to get access to the underlying
> RibbonButton?
>

[Michael]

Hi Govert,

GUI:
----
Thanks for the detailed reply. After looking through the ExcelDNA
code, I now understand the restrictions that are placed on the DNA
interface. The VSTO interface is certainly 'interesting' at the very
best. It would be nice if the office tools ribbon gui components had
ToXML and FromXML. It would facilitate their usage in other
applications.

We are going to use the Invalidate method with a hand coded XML as it
is fairly trivial.

regards
Michael

On Mar 20, 7:26 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Michael,
>
> This is part two of my reply, about the Ribbon.
>
> Ribbon GUI:
> -----------

> This thread has many Ribbon-related links:http://groups.google.com/group/exceldna/browse_thread/thread/1db415c2....

[Govert van Drimmelen]

Hi Michael,

AFAIK there is an 'Export to XML' option from the VSTO Ribbon
Designer.

-Govert