Ribbon bar Not working / Showing

Oct 9, 2012 at 5:35 PM

I am very new to using Excel DNA, I started using it after I as asked to create some custom funcitons for Excel.  But I didn't want to code it in VBA.  So I have created my first custom function and now I am needing to add a ribbon bar command button to work with that function. 

I am using Visual Studio 2010 Pro, and I have UAC disabled and I run as the Admin.

Here is my FirstAddIn.dna code:

<DnaLibrary Name="GL Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyLibrary.dll" />
  <CustomUI>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
      <ribbon>
        <tabs>
          <tab id="customTab" label="General Ledger" insertAfterMso="TabHome">
            <group id="Refresh" label="Refresh">
              <button id="RefreshCmd" label="Reload Data" imageMso="QueryMakeTable" onAction="RefreshCmd" />
            </group>
          </tab>
        </tabs>
      </ribbon>
    </customUI>
  </CustomUI>

</DnaLibrary>
And here is my C# code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

namespace MyLibrary
{
    public class MyRibbon : ExcelRibbon
    {
        
        public void RefreshCmd(IRibbonControl control)
        {
            //Years.Instance.Year(2012).Set_Trial_Balance_Data();
            //Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
            MessageBox.Show("Refresh Done!"); 
            
        }
    }

    public class MyFunctions
    {
        [ExcelFunction(Description = "GL Summary")]
        public static Double ACT(String Year, String Period, String OU_ID, String Account)
        {
            Year yr;
            Parameter parYear;
            Parameter parPerd;
            Parameter parOUID;
            Parameter parAcct;
            Double Amnt = 0;
            String flt;

            parYear = new Parameter("Year", Year);
            parPerd = new Parameter("Period", Period);
            parOUID = new Parameter("OU_ID", OU_ID);
            parAcct = new Parameter("Account", Account);

            foreach (Criteria c in parYear)
            {
                yr = Years.Instance.Year(Convert.ToInt16(c.Par1));
                flt = parPerd.Filter;
                flt += " AND " + parOUID.Filter;
                flt += " AND " + parAcct.Filter;

                foreach (DataRow r in yr.Trial_Balance_Data.Select(flt))
                {
                    Amnt += Convert.ToDouble(r["Amount"]);
                }
            }

            return Amnt;
        }

    }
}
The MyRibbon Class is where I am just testing to see if my RefreshCmd works or not.  
But the issue is that I don't even have the custom ribbon bar showing.
Any help with this would be great, once I have these basic items outof the way I can start creating the rest of my project.
Coordinator
Oct 9, 2012 at 5:48 PM

Hi,

The class derived from ExcelRibbon needs to be 'COM Visible'. You can ensure this either by:

  • Marking the class with the [ComVisible(true)} attribute, or
  • Ensuring the assembly-wide default is set to [assembly(ComVisible(true)] in the Properties\AssemblyInfo.cs file.

The default C# library project in Visual Studio sets the assembly-wide attribute to [assembly:ComVisible(false)]. There is a project property that also sets this - be sure to set 'COM Visible' to true, but keep 'Register for COM Interop' to false.

For the code in the .dna file examples this is not a problem, since the .NET default (if there are no attributes) is to make types visible to COM. It's just the Visual Studio template that has the opposite default, and forces you to change or override something.

There are other reasons why your ribbon might not load:

  • An error in the .xml,
  • your add-in is disabled by Excel - check under File->Options->Add-Ins->Manage: Disabled Items.
  • there was an issue with UAC and admin that I fixed in recent check-ins, but I think this affects the case where UAC is enabled, and you explicitly run Excel as an admin.

Write back if you need some more ideas to try.

Regards,

Govert

Oct 9, 2012 at 8:20 PM

Thanks that resolved my issue changing the assembly COM Visible to true.