How to Call Excel Function from Ribbon?

Feb 14, 2012 at 9:30 AM

Hi,

I've written one Excel Function which i want to call, when user click on Ribbon button. My Function signature is as below.

[ExcelFunction(Name = "MyFunctions.GetCustomData", Description = " ", Category = "MyFunctions", IsMacroType = true )]

Also, How can I provide description for my parameters, so that when i type function in Excel Cell, it should give me some intellisence for my parameters.

public static object GetOutstandingSecurityListAsOnDate(stringparam1, int param2)

=MyFunction(string param1, int param2...)

Regards,

Leo

Coordinator
Feb 14, 2012 at 11:43 AM

Hi Leo,

For descriptions to your parameters you can add [ExcelArgument(Description="...")] attributes before the parameters.

This will give descriptions in Excel's Function Arguments dialog box. Excel does not support extending the in-cell pop-up Intellisense, it only works for built-in functions.

I'm not sure whether your first question is about how to run code from the ribbon handler. But there are some ribbon examples in the Excel-DNA distribution.

Regards,

Govert

Feb 14, 2012 at 12:51 PM

Thanks govert for your Quick response here.

I got your reply regarding ExcelArgument(Description..

For ribbon,

I've added one ribbon in my excel. In my .dna file, I'm Calling MyRibbonClicked() function.

I've Ribbon.cs file

public void OnMyRibbonClicked(IRibonControlControl)

{ 

 

 

 

try{ 

MessageBox.Show("OnMyRibbonClicked"); 

 >>>>>>>>>>GetDATAFromDB() >> Which is My ExcelFunction as below;

}

 

 

 

 

 

DataTable dtResult = GetValuesFromDB();

object[,] objResult = GetCommaSepratedValuesFromDataTable(dtResult)

return XlCall.Excel (XlCall.xlUDF,"Resize",objResult)

But seems like it's not working.

Regards,

Leo

Coordinator
Feb 14, 2012 at 2:26 PM

Ah - I think I see the problem.

You are calling the Excel C API (via XlCall.Excel) but you are in the context of a ribbon handler. The C API is not supported in the ribbon handler - you have to make an Excel-DNA macro (public static void ...) and then call this macro via Application.Run from your ribbon handler if you want to call the C API.

An alternative is to use the COM interface (getting the Application object via ExcelDnaUtil.Application), which is safe to call from your ribbon handler.

-Govert

Feb 21, 2012 at 4:35 AM

Hi Govert,

An alternative is to use the COM interface (getting the Application object via ExcelDnaUtil.Application), which is safe to call from your ribbon handler.

Do you have ane example or post for this? So i can try to implement in that way. Honestly, I don't have any idea to user COM Interfaca but will try it if i get any hint for that.

Regards, Leo

Coordinator
Feb 21, 2012 at 6:29 AM
Edited Feb 21, 2012 at 6:30 AM

Hi Leo,

It looks like you're using C# - I'm assuming you are using .NET 4 with a RuntimeVersion='v4.0' tag in your .dna file. C# 4 makes the COM interoperability a lot better. 

In your project, add a reference to the assembly "Microsoft.Office.Interop.Excel".

Then your code might look something like this:

 

using Excel = Microsoft.Office.Interop.Excel;

public class MyRibbon : ExcelRibbon
{
   public void OnMyRibbonClickec(IRibbonControl control)
   {
      Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;

      // ... Call your function to get the data and put into an array
      // object[,] myDataArray = .....

      xlApp.ActiveSheet.Range["A1:C3"].Values = myDataArray;
   }
}

 

The variable xlApp refers to an Excel 'Application' object, and the whole object model hierarchy below it is available from your code. 

Here is an introduction to the Excel COM Object mode: http://msdn.microsoft.com/en-us/library/gg192737.aspx, with the Excel object model reference here: http://msdn.microsoft.com/en-us/library/ff194068.aspx

Regards,

Govert