Menus and Ribbons in Visual Studio, and RuntimeVersion="v3.5"

[aberglas]

Hello,

I have not been able to get this to work. In a class

[ExcelCommand(MenuName = "DnaDetTest", MenuText = "Set with
COM")]
public void SetValueCOM() {...

Does not produce a menu. Do I need to do something else?

Solved -- to make the Ribbon work you need to add [ComVisible(true)]
to the MyRibbon class. (Weirdly, if I rename it to MyRibbonX it still
works -- a bit spooky the way it finds classes.)

Also a bug to report, to run with .Net 3.5 one needs
RuntimeVersion="v2.0" -- I eventually guessed this as they use the
same runtime but should be easy to fix to avoid the pain for others.

Thanks,

Anthony

[Govert van Drimmelen]

Hi Anthony,

1. Like the user-defined functions, your macros ('void' methods) must
be static for Excel-DNA to recognize and register them as macros.
In VB.NET they can be either 'Shared' members of a class, or methods
in a Module (where everything is implicitly 'Shared'.
So you need something like:

[ExcelCommand(MenuName = "DnaDetTest", MenuText = "Set with COM")]

public static void SetValueCOM() {... }

2. Excel-DNA knows that your class is a ribbon, and registers it with
Excel, if it derives from ExcelDna.Integration.CustomUI.ExcelRibbon.

3. The RuntimeVersion attribute in the .dna file works as intended.
The .NET Framework 3.5 did not introduce a new version of the runtime,
just additional libraries. The unmanaged loader in Excel-DNA is just
concerned with which version of the runtime to load the add-in into,
and the options are either RuntimeVersion="v2.0" for the .NET
Frameworks 2.0, 3.0 and 3.5, or RuntimeVersion="v4.0" for the .NET
Frameworks 4.5 and 4.5. The default, if nothing is specified, is to
load version 2.0.

Here is a discussion that has more details on how your add-in might
detect whether the .NET 3.5 libraries are also installed:
http://groups.google.com/group/exceldna/browse_frm/thread/8d61c6c1e6e758c7.
Currently Excel-DNA just takes care of the aspects that your managed
add-in couldn't implement. But there's no technical reason why the
further checks couldn't be incorporated into Excel-DNA too, instead of
you putting the code in your add-in.

Regards,
Govert

[aberglas]

Hello Govert,

Thanks again for your considered and prompt replies.

1. Oops, yes I should have seen that the VB code was a Module.
However I have added the static and it still does not work for me. No
matter, I do not need this for now. But an error message would be
good.

2. I understand how Excel-DNA finds the ribbon, but it is still
spooky. Just like making every static function a UDF is spooky. What
if I need a class that extends ExcelRibbon that is not supposed to be
displayed for some reason? More importantly, there is nothing in the
code to indicate that it is being used in this way. I would suggest
you avoid this sort of thing. Either an explicit [...] annotation, or
some way to register interesting classes.

3. Yes, I guessed why it needed 2.0. But it cost me a good hour. How
hard would it be to write
if (vsn == "v2.0" || vsn = "v3.0" || vsn = "v3.5") { // load the
2.0.... }
else...
else {
ErrorMessage("RuntimeVersion not v2.0, v3.0, v3.5 or v4.0");
}
// Removing the need to specify Runtime Version would of course be
better. But the above would suffice.

4. The ErrorMessage part is critical. Where do error messages go? A
dialog? a log file?

**** Good diagnostics are the difference between easy to use software
or a world of pain. ****

"SetValueCOM must be static" would be another good message.

5. If I get my little test vs project into a tidy state, would you be
interested in incorporating it in the Excel DNA samples? (That means
you need to review and be happy with it -- Samples must not
degenerated into a pile of vaguely correct user junk. And it needs to
be maintained.)

6. I would also suggest a Wiki for your documentation. Then we can
make contributions as we find issues. And instead of detailed replies
to this group, you could update the docs and just point us to them.
Over time, less work for you.

I missed your earlier reply about COM, am looking at it now...

Thanks,

Anthony

On May 19, 8:06 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Anthony,
>
> 1. Like the user-defined functions, your macros ('void' methods) must
> be static for Excel-DNA to recognize and register them as macros.
> In VB.NET they can be either 'Shared' members of a class, or methods
> in a Module (where everything is implicitly 'Shared'.
> So you need something like:
>
>   [ExcelCommand(MenuName = "DnaDetTest", MenuText = "Set with COM")]
>   public static void SetValueCOM() {... }
>
> 2. Excel-DNA knows that your class is a ribbon, and registers it with
> Excel, if it derives from ExcelDna.Integration.CustomUI.ExcelRibbon.
>
> 3. The RuntimeVersion attribute in the .dna file works as intended.
> The .NET Framework 3.5 did not introduce a new version of the runtime,
> just additional libraries. The unmanaged loader in Excel-DNA is just
> concerned with which version of the runtime to load the add-in into,
> and the options are either RuntimeVersion="v2.0" for the .NET
> Frameworks 2.0, 3.0 and 3.5, or RuntimeVersion="v4.0" for the .NET
> Frameworks 4.5 and 4.5. The default, if nothing is specified, is to
> load version 2.0.
>
> Here is a discussion that has more details on how your add-in might

> detect whether the .NET 3.5 libraries are also installed:http://groups.google.com/group/exceldna/browse_frm/thread/8d61c6c1e6e....

[Govert van Drimmelen]

Hi Anthony,

> 1.  [...] I have added the static and it still does not work for me.

Remember that the class should also be public. This should certainly
work:

public class MyStuff
{

[ExcelCommand(MenuName = "DnaDetTest", MenuText = "Set with
COM")]
public static void SetValueCOM() {... }
}

Also note that, under Excel 2007+, the old-style add-in menus appear
under an 'Add-Ins' tab in the ribbon.

> 2. [...] Just like making every static function a UDF is spooky. [...]

This was done to mirror what you have in VBA, where every public
function in a module is exposes as a worksheet UDF.

More recently, I added an option (ExplictExports="true") that you can
set in the .dna file to only export functions and macros that are
explicitly marked by [ExcelFunction] or [ExcelCommand]

>  What if I need a class that extends ExcelRibbon that is not supposed to be
> displayed for some reason?

The only purpose of the Excel-DNA ExcelRibbon class is to make a
ribbon. You need to control whether and how it is displayed using the
ribbon xml and callback handlers. There is no other reason to derive
your class from ExcelRibbon.

> More importantly, there is nothing in the code to indicate that it is being used in this way.

ExcelRibbon should be used in no other way.

> I would suggest you avoid this sort of thing.  Either an explicit [...] annotation, or
> some way to register interesting classes.

This is already in place for functions and macros. For the ribbon we
have the base class that you derive from for technical reasons.

> 3. Yes, I guessed why it needed 2.0.  But it cost me a good hour.  How
> hard would it be to write
>   if (vsn == "v2.0" || vsn = "v3.0" || vsn = "v3.5") { // load the

Excel-DNA mirrors the .NET runtime loading APIs in this respect. There
is no "v3.0" runtime, and Excel-DNA is not checking whether the
additional v3.0 libraries are installed. The RuntimVersion attribute
is used by the unmanaged code (where we want to do as little as
possible) just to decide which version of the runtime to load.

>   // Removing the need to specify Runtime Version would of course be
> better.  But the above would suffice.

This is in place. If you are targeting the .NET Framework 2.0, 3.0 or
3.5 you do not need to specify the runtime version. The default is to
load the .NET 2.0 runtime. Only if you want to target .NET 4.0 do you
have to specify the RuntimeVersion in the .dna file.

> 4. The ErrorMessage part is critical.  Where do error messages go?  A
> dialog? a log file?

Fair comment. Critical errors are displayed as a message box or in the
LogDisplay window, and all the registration stuff is written to the
debugger output. But there is no general logging in place. It would be
a good improvement to have some standard logging bits, allowing you to
plug in whatever logging library you want to use (NLog or whatever).
On my list, but not very high at the moment.

> "SetValueCOM must be static" would be another good message.

I agree - if a function or macro is marked with ExcelFunction /
ExcelCommand and cannot be registered for any reason, a message should
be shown. Small problem with this is that I only search for static
methods, before checking whether the have the attributes.

> 5. If I get my little test vs project into a tidy state, would you be
> interested in incorporating it in the Excel DNA samples?  (That means
> you need to review and be happy with it -- Samples must not
> degenerated into a pile of vaguely correct user junk.  And it needs to
> be maintained.)

First prize for me would be if you made a public add-in that you
maintain and that you find useful, similar to FinAnSu (http://
brymck.github.com/finansu/) or Dodoni.net (http://
dodoni.codeplex.com/).

But of course I'd appreciate and look at anything you submit. I'm
pretty cautious about maintaining after other people's code though - I
expect to support this stuff for many years to come, so I move slowly
at whatever pace I am comfortable with.

> 6. I would also suggest a Wiki for your documentation.

CodePlex has that - I'd be happy to add you as an editor on the
project.

Regards,
Govert