Ranges in Excel with Excel DNA

[Robert Berry]

Hi,

I can't seem to figure out how to work with Ranges with Excel DNA. As a simple test and hopefully a simple explanation, I would simply like to sum a range of values.  How would you do this?

Regards,

Rob.

[Govert van Drimmelen]

Hi Rob,

How you work with ranges might depend on whether this is within a
function or from a macro, and whether you want to use the COM
automation interface or the native Excel API. So there are a number of
options...

To create a worksheet function that adds the values in a range that is
passed in as an argument, you'd just create a function that takes an
array of object or double as it's input:
In VB.NET:
Public Shared Function MySum(values As Double(,)) As Double
{
Dim total As Double
' Calculate based on values array.
Return total
}
and in C#:
public static double MySum(double[,] values)
{
//....
}

If you are creating a macro and want to read a range, you can use the
COM automation interface, and it looks a lot in VBA:
Public Shared Sub TestRange()
{
Dim xlApp As Object
Dim myRange As Object
Dim values As Object

' Need to get the Application object
' that is implicitly available in VBA
xlApp = ExcelDnaUtil.Application
myRange = xlApp.Range("A2:B5")
values = myRange.Value

// Use values here....
}
How you'd use the COM automation interfaces from C# depends on whether
you are targeting a single Excel version and whether you are able to
use .NET 4.

If you want to write high-performance reads and writes for ranges,
you'd use the .xll API through the XlCall and ExcelReference types in
Excel-DNA. A nice example of this approach is in a recent
StackOverflow answer:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-objects/3868370#3868370


I'm sure the variety of options will raise more questions in return.
But this should give you an idea of the factors that influence your
approach.
If you can give more details of your environment and what you are
trying to do, I can try to make a nice example.

Regards,
Govert