What Excel versions are supported by Excel-DNA

Mar 26, 2014 at 9:46 PM
Hi,

I was wondering what Excel versions are supported by Excel-DNA. My add-in works in Excel 2007 and 2010. When testing against Excel 2003 however, I do not see the Add-Ins tab and functionality is not working.

My .dna file has the custom UI provided below. The Add-Ins tab does not appear in Excel 2003 though the Add-In is present and is selected. Other functionality is also not working in this version of Excel.

Is there anything that I need to do special for Excel 2003?

Thanks,

Lee
<CustomUI>
<!-- Inside here is the exact RibbonX xml passed to Excel -->
<!-- This will only be loaded for Excel 2010 because of the namespace -->
<customUI onLoad="XLLRibbon_Load" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabAddIns">
        <group id="xllGroup" label="Menu Commands">
          <menu id="mCreditRatingsMenu" label="XXXX">
            <button id="xxxData" label="CMBS Data" onAction="dpXXX_Action" />
            <button id="xxxDataDct" label="Data Dictionary" onAction="dpXXX_Action" />
            <button id="xxxFeedback" label="Send Us Feedback" onAction="dpXXX_Action" />
            <button id="xxxAbout" label="About XXXX Excel Add-In API" onAction="dpXXX_Action" />
          </menu>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
</CustomUI>
Coordinator
Mar 26, 2014 at 9:53 PM
Hi Lee,

Excel-DNA should work fine with add versions of Excel since Excel '97.
However, there are some feature differences:
  • Ribbons and Custom Task Panes are only supported in Excel 2007+. For older versions of Excel you can use the regular menu support from the [ExcelCommand] attribute, like this:
[ExcelCommand(MenuName="My Test Menu", MenuText="Do stuff")]
public static void DoStuff()
{
}
More advanced menus will need to use the Office CommandBars API to make nested menus etc. There are some example on how to use CommandBars with Excel-DNA in the Distribution\Samples\Ribbon directory.
  • RTD-based features like Async functions are only supported in Excel 2002+.
In addition, all strings are limited to 255 characters under Excel 2003.

Are there any other features that seem not to be working on Excel 2003?
What version of Excel-DNA are you using?

-Govert
Mar 27, 2014 at 5:54 PM
Hi Govert,

I am using version 0.31.11.1. I modified the .dna file as shown below from what I could find in the discussion groups. Now the menu appears, but I do not see the buttons on it. There are very narrow empty lines under the menu. If I click on one of the empty lines, I get the message "The macro 'dpXXX_Action' cannot be found.".

Can I do this implementation from the .dna file and if so, what else do I need to do for this to work?

I will attempt to investigate what else is not working, but the only other functionality that I can get to without the menu is a formula and the formula is being accessed but something is breaking behind the scenes. I will attempt to determine if the test VM has an issue with required features missing or if something else is happening.

Thanks,
<CustomUI>
<!-- Inside here is the exact RibbonX xml passed to Excel -->
<!-- This will only be loaded for Excel 2010 because of the namespace -->
<customUI onLoad="XLLRibbon_Load" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
  <tab idMso="TabAddIns">
    <group id="xllGroup" label="Menu Commands">
      <menu id="xxxMenu" label="XXXX">
        <button id="xxxData" label="CMBS Data" onAction="dpXXX_Action" />
        <button id="xxxDataDct" label="Data Dictionary" onAction="dpXXX_Action" />
        <button id="xxxFeedback" label="Send Us Feedback" onAction="dpXXX_Action" />
        <button id="xxxAbout" label="About XXXX Excel Add-In API" onAction="dpXXX_Action" />
      </menu>
    </group>
  </tab>
</tabs>
</ribbon>
</customUI>
</CustomUI>
<CustomUI>
<!-- CommandBars Fallback for Excel version pre-2007, interpreted by ExcelDna (I just made up the namespace for future versioning) -->
<commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars' >
<commandBar name='Worksheet Menu Bar'>
  <!-- Here we add a top-level menu to the main Excel Worksheet menu -->
  <popup caption='xxxx'  >
        <button id="xxxData" label="CMBS Data" onAction="dpXXX_Action" />
        <button id="xxxDataDct" label="Data Dictionary" onAction="dpXXX_Action" />
        <button id="xxxFeedback" label="Send Us Feedback" onAction="dpXXX_Action" />
        <button id="xxxAbout" label="About XXXX Excel Add-In API" onAction="dpXXX_Action" />
  </popup>
</commandBar>
</commandBars> 
</CustomUI>
Coordinator
Mar 27, 2014 at 6:16 PM
Hi Lee,

From the examples in Distribution\Samples\Ribbon, it looks like you need to use "caption" instead of "label" for the CommandBar buttons.

The onAction handler will look for proper macros in your code (public static void) to run.

There are a variety of ways to implement the CommandBars, either in .dna code or programmatically. You can look at the example for some ideas.

You can wrap the UDF function in an exception handler to see what might be wrong, or register a global ExcelIntegration.RegisterUnhandledExceptionHandler.

-Govert
Mar 27, 2014 at 7:09 PM
Thanks for the quick response Govert,

I see that I need a static method like the one that I am using for ribbons. For ribbons, I am able to have a parameter IRibbonControl control that I can get the button which sent the request. Is there a similar event for the static function?
    public void dpXXXX_Action(IRibbonControl control)
    {
        Microsoft.Office.Interop.Excel.Application excelApp;
        .
        .

        try
        {
            .
            .

            // Check the version of the add-in
            Utilities.CheckVersion();

            // Check the session
            if (CheckSession())
            {
                excelApp = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;
                if (excelApp.ActiveWorkbook == null)
                {
                    facade.ShowMessageBox("Please open a workbook");
                }
                else
                {

                    switch (control.Id)
                    {
                        case "xxxData":
                            frmCmbsData cmbsData = new frmCmbsData();
                            dlResult = cmbsData.ShowDialog();
                            break;
                        case "xxxDataDct":
                            frmDataDictionary dd = new frmDataDictionary();
                            dlResult = dd.ShowDialog();
                            break;
                        case "xxxFeedback":
                            SendFeedback();
                            break;
                        case "xxxAbout":
                            ShowAbout();
                            break;
                        default:
                            break;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            ...
        }
    }
For instance on the menu below (not sure if I need to specify the ExcelCommand if it is done in the .dna file), how can I tell what button was hit if all buttons use the same event (i.e. macro)
[ExcelCommand(MenuName="mCreditRatingsMenu", MenuText="mCreditRatingsMenu")]
public static void  dpXXXX_ActionCommandBar()
{
}
Coordinator
Mar 27, 2014 at 7:17 PM
Hi Lee,

I think it's possible to set up parameters for the onAction, but it's hard. Google found this: https://www.google.co.za/search?q=excelcommand+parameters+onaction&rlz=1C1CHMO_enZA530ZA530&oq=excelcommand+parameters+onaction&aqs=chrome..69i57j0l5.8526j0j7&sourceid=chrome&espv=210&es_sm=93&ie=UTF-8#q=excel+command+parameters+onaction&safe=off&spell=1

Otherwise, you can create the CommandBars programmatically, and attach event handlers. See the examples and the Office CommandBars documentation.

Easiest is just to make different functions...

-Govert
Marked as answer by lzeitz on 4/1/2014 at 11:42 AM
Mar 27, 2014 at 7:54 PM
Edited Mar 27, 2014 at 8:01 PM
Hi Govert,

Thanks for all of your assistance.

I am able to access most of the functionality now, but the onAction is being executed twice every time a button is clicked. Is there a way to prevent the extra event?

My macro is of the form:
    [ExcelCommand]
    public static void dpMorningstarCR_ActionCommandBar(int nAction)
and is called from
<CustomUI>
<!-- CommandBars Fallback for Excel version pre-2007, interpreted by ExcelDna (I just made up the namespace for future versioning) --> 
<commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars' >
<commandBar name='Worksheet Menu Bar'>
<!-- Here we add a top-level menu to the main Excel Worksheet menu -->
<popup caption='xxxx'  >
    <button id="xxxData" caption="CMBS Data" onAction="dpXXX_Action" />
    <button id="xxxDataDct" caption="Data Dictionary" onAction="dpXXX_Action" />
    <button id="xxxFeedback" caption="Send Us Feedback" onAction="dpXXX_Action" />
    <button id="xxxAbout" caption="About XXXX Excel Add-In API" onAction="dpXXX_Action" />
</popup>
</commandBar>
</commandBars> 
</CustomUI>
Coordinator
Mar 27, 2014 at 7:59 PM
It's just something you're doing funny.
Maybe there are multiple add-ins loaded, or you've got things in different places.

Or you can go back to the sample, check that the stuff runs only once there, and work your way back again.

-Govert
Apr 1, 2014 at 6:17 PM
Govert,

I would like to thank you for all of your help. I finally got a VM setup that I could actually test my code on. The issue with the "macro" specified by the onAction command had to do with the format.

I was attempting to call a single onAction command and pass in a different parameter.

For example
<popup caption='xxxx'  >
    <button id="xxxData" caption="CMBS Data" onAction="dpXXX_Action(1)" />
    <button id="xxxDataDct" caption="Data Dictionary" onAction="dpXXX_Action(2)" />
    <button id="xxxFeedback" caption="Send Us Feedback" onAction="dpXXX_Action(3)" />
    <button id="xxxAbout" caption="About XXXX Excel Add-In API" onAction="dpXXX_Action(4)" />
</popup>
While this works, this method of calling the macro causes Excel to run twice. I could not find a method that could be performed within the dna file for using a single macro that was recognized by Excel.

I had to create 4 different versions of the function and call my original function behind the scenes.
<popup caption='xxxx'  >
    <button id="xxxData" caption="CMBS Data" onAction="dpXXX_Action_1" />
    <button id="xxxDataDct" caption="Data Dictionary" onAction="dpXXX_Action_2" />
    <button id="xxxFeedback" caption="Send Us Feedback" onAction="dpXXX_Action_3" />
    <button id="xxxAbout" caption="About XXXX Excel Add-In API" onAction="dpXXX_Action_4" />
</popup>
where dpXXX_Action_n calls another function with the signature
    public static void dpMorningstarCR_ActionCommandBar(int nAction)
    {
            .
            .
            .
            .
    }