Can we call the excel-dna C# library's class in VBA?

Mar 5, 2013 at 7:24 AM
Can we call the excel-dna C# library's class like below in VBA?

Dim p as plugin -- plugin is a C# class
p.CalculateAllData(); -- this function will calculate the data and save the result to a property in the class.
p.GetCalculateDataByName("Name1"); -- this function will get part of data from the saved property in the class.
p.GetCalculateDataByName("Name2");

Thanks,
Rock
Coordinator
Mar 5, 2013 at 10:31 AM
Hi Rick,

You can, but it's quite tricky to get it all working the first time. See here: http://exceldna.codeplex.com/wikipage?title=COM%20Server%20Support for a start, and then search for "COM Server" on the Google group, and look for the COMServer example in the distribution.

-Govert
Mar 5, 2013 at 12:37 PM
I'm sorry I cannot find the sample. Could you help to give me some sample or documentation? Thanks.
Coordinator
Mar 5, 2013 at 1:11 PM
Hi Rick,

It's in the Distribution\Samples\ComServer directory in the Excel-DNA download.

-Govert
Mar 18, 2013 at 2:59 PM
Thank you very much. I can call the C# in the excel. May I know how to debug the C# function when I call the c# library in the excel VBA function. I attached the excel process,but it not works.

Thanks,
Rock
Coordinator
Mar 18, 2013 at 3:06 PM
Hi Rock,

When attaching to the Excel process, be sure to select the right version of the Managed debugger. If you have RuntimeVersion="v4.0" in your .dna file, then pick .NET 4.0 else pick .NET 2.0.

Otherwise I suggest you also make a simple UDF in the C# side, and check that you can debug this first.

-Govert
Mar 20, 2013 at 1:25 PM
I think I selected the right version. It seems the Visual Studio can't attached the dll by the COM.

Here is my steps to do it.
  1. Used the csc.exe to compile the cs files to generate the dll.
  2. Used tlbExp.exe to generate the tlb file.
  3. Used ExcelDnaPack.exe to pack the xll file.
  4. Used regsvr32.exe
By the way, I have a C# function like below.

Public object[,] GenerateData(object[,] prices)
{
...
}

In the VBA:
Dim service As New GenerateService
prices= Range("Price")
flag = service.GenerateData(prices)

But it doesn't work. I can't parse the object[,] prices in C# function. Could you advise?

Thanks,
Rock
Coordinator
Mar 20, 2013 at 1:57 PM
Hi Rock,

Are you able to debug a plain UDF created in the add-in, and called from a worksheet?

For your GenerateData function, you might rather pass the values in your range (the result of Range("Price").Value), or do you mean to deal with the Range COM object in C#?

-Govert
Mar 22, 2013 at 9:55 AM
I would like to use the value. It works when I change object[,] to object.

Public object[,] GenerateData(object prices)
{
...
}

And I could create a testing project to debug the C# API.

Thank you very much.
Rock