Best way to pass Ranges from Excel through ExcelDNA to DLL and place results back into Range(s) using C#

[DT]

Lots of examples on Group... but none simple enough for me. LOL.

I am atempting to use ExcelDNA to link Excel to a C# DLL (that
contains proprietary algorithms).
I really just need to bridge between the DLL and Excel (with Ranges)
and want to keep the processing within the .DNA file to a minimum....
Here's a basic .DNA. What would work, what not?

Trying to run .NET 4.0, etc.

THANK YOU!!!!

<DnaLibrary Language="C#" RuntimeVersion="v4.0">
<Reference Name="Microsoft.Office.Interop.Excel" />
<Reference Name="MyCSharpDLL" />
<Reference Path="c:\MyCSharpDLL.dll" />

<![CDATA[
using Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

public static class TestClass
{

[ExcelCommand(MenuText="Say Hello")]
public static double ProcessRanges(Range x, Range y, Range, z)
{
return MyCSharpDLL.DLLClass.DoWorkOnRanges(x, y, z);
}
}


]]>
</DnaLibrary>

[Govert van Drimmelen]

Hi Doug,

There are a few issues in your example.

1. You need to decide whether this will work as
- a worksheet function - entered as the formula into a cell, which
taking some ranges an input and returns a single value or an array
(which you enter into Excel with Ctrl+Shift+Enter), or
- a command macro, which you trigger from a menu or ribbon button,
which takes no parameters and can examine and change any part of the
sheet. Here you might use named ranges or something similar to
identify your 'input' and 'output' ranges.

Your current function is kind of a mix - it looks like a function
but has the ExcelCommand attribute which would normally be used for a
command macro.

2. Suppose you want to make a worksheet function, which takes three
arrays of values and returns a double (doing no 'work' on the ranges).
Then you'd change the parameter types to 'object[,]' in order to get
arrays with the input values, which you then process and return some
'double' value from.

I don't know if you can change the MyCSharpDLL library. If you can, we
might be able to simplify things a bit by changing the function there
to takes object[,] arrays as input instead of the 'Range' COM objects.

3. Excel-DNA functions can't (currently) take Range COM objects as
inputs. There is a way to get a reference to a part of the sheet, from
which you can reconstruct a Range if you really need to, but that's a
bit more advanced.

4. You don't need the <Reference> to MyCSharpDLL twice - only the one
with the 'Path' attribute is being used. The path can be absolute like
you have it now, or relative to the location of the .xll file.

5. If you are able to change the MyCSharpDLL, you can get rid of all
the code in the .dna file, replacing it with an ExternalLibrary tag:

<DnaLibrary RuntimeVersion="v4.0" >
<ExternalLibrary Path="MyCSharpDLL.dll" />
</DnaLibrary>


-----

I think that's a start.
Maybe you can send some more information about what you are trying to
do with your function or command.

Regards,
Govert

[DT]

Hi Govert!
So glad that you took the time to reply - many thanks.

Here is some more info:
1. It should/will work as as a command macro from menu/ribbon.
2. I have full control over the C# DLL, and I can definitely modify
the arguments for the functions within it.
3. I would gladly convert *away* from Range objects (and attemtpt to
follow your recommendations). However, the results would need to be
returned into a sheet at the location(s) specified as one of the
parameters (input, second_input, output).
4. I also have a need to alter cell comments & fill colors... are
those properties exposed through ExcelDNA? (current non-DNA approach
does modify comments and fill colors, but has been awful to get bug
free - hence the move to DNA).

Thank you!
Doug

> > </DnaLibrary>- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi Doug,

When pressing the menu button, how will you decide which ranges are to
be used as input, and how are the result locations specified?

You should think of your menu button as running a function that has no
parameters and does not return anything:

[ExcelCommand(MenuName="My Excel-DNA Add-In", MenuText="Do my stuff")]
public static void DoMyStuff()
{

// Here you might show a dialog box to the user, or retrieve some
named ranges.

}


From command macros, Excel-DNA supports both the COM automation
interfaces and the native C API.

Easiest as a first step would be to use the same code you'd use in
VBA, which does use COM automation types like Range etc.
Later we can convert this to use the native C API (using types like
ExcelReference instead of Range).

The C API does not give you access to all aspects of Excel, but
comments and cell colors can be changed from the C API.

---

Can you explain a bit more about the current code you have that works
(though it might be buggy)?

Regards,
Govert

[douglas thompson]

Hi Govert!
I checked the old code and I used a .xla file that instantiated a global public instance of the c# class.  Guess that its definitely one reason for the poor performance.... Lol.  I guess I used com to connect the xla to the c sharp dll...right?

As for buttons I would like the user to select Button labeled input and have a popup dialog to select cells...which could be discontinuous or a continuous set. The user would have a button for second input and a third button for specifying where to place the output.

The dll would also also be used with a console app so your idea of translating the ranges into more c sharp friendly types would also help in building the console app interface.  Do I guess the dna file would handle the translations between excelish and c sharp types?

So far the only extra things that I set within the result cell sets is value, comments and fill color.

My sincere thanks for your time and advice... And a sincere apology for the elementary level questions. :-)

Warm regards
Doug

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Doug,

I attach two .dna file which might give you some ideas for how to go about this in C#, with Excel-DNA.

The first example (RangeConvert.dna) uses the COM interfaces - so the Application and Range types you know from VBA. To ease the access from C# I use the dynamic COM support in C# 4 so that I don't have to deal with interop assemblies in this example. For real code there are some decisions you need to make about how to deal with the COM interop assemblies, or possibly to use a version-independent set of interop assemblies like NetOffice (http://netoffice.codeplex.com).

The main disadvantage of the 'dynamic' approach is that you don't get Intellisense support.

You'll see the handling of the values retrieved from the Range is a bit messy, since Excel will return arrays that are 1-based, and the C# then gets a bit verbose. For real code you'd write a wrapper to deal with this.

Also there is no error handling - you need to select a range with a few cells for the input, and a range of the same size for the output.

The second sample (RangeConvertCAPI.dna) uses the C API. The code is simpler because those arrays are 'normal', but there is a substantial learning curve in getting to know the C API (used with Excel-DNA through the XlCall and ExcelReference types). The documentation is in an old help file from Excel '97, called XLMACR8.HLP file (which you can download from here: 

http://www.microsoft.com/download/en/details.aspx?id=24039 ).

Performance of both examples, even for huge ranges, will be fine. The C API will be faster, but likely not enough to impact your decision.

For macros that manipulate the sheet like this, my recommendation would be: If you are already familiar with the C API, I suggest you go that route, otherwise go the COM route. In the end the COM interfaces give more power in manipulating Excel than the native C API.

-----

A good first step would be to get the two sample .dna files to run on your machine, then to put the code into your own .dll instead of the .dna file and try to get it to work from there (both ways can go into the same .dll happily).

I'm sure you'll have many more questions after that..

Regards,

Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of douglas thompson [doug...@gmail.com]
Sent: 06 March 2012 06:13 PM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: Best way to pass Ranges from Excel through ExcelDNA to DLL and place results back into Range(s) using C#

[douglas thompson]

Hi Govert -
Thanks again for all your time & effort on this. I will take a look
at the files that you attached to see what works best. Outstanding!

Cheers,
Doug

[Govert van Drimmelen]

Try this...

C API Version (RangeConvertCAPI.dna):
https://gist.github.com/3012444

COM Version (RnageConvert.dna):
https://gist.github.com/3012438

-Govert

On Jun 28, 5:40 pm, ocean <ocean.ind...@gmail.com> wrote:
> Not able to download these files...

> > > douglas thompson [dougt...@gmail.com]

> ...
>
> read more »