Re: Using the equivalent of VBA's Application.Intersect function

[Govert van Drimmelen]

Hi Bruce,

I'll give some background first, which might help you or others on
this list running into it later.

Excel supports two completely different programming interfaces:
1. The COM Automation interface that you know from VBA.
2. The native C API described in the Excel XLL SDK.

Excel-DNA allows you to use either approach, and to mix them in your
add-in to some extent.

Excel-DNA integrates into Excel using the C API. The following types
are related to the C API:
* ExcelReference, which is a thin wrapper around a worksheet
reference, and
* XlCall.Excel and the XlCall constants, which give you a .NET
interface based on the xlcall.h file that defines the C API.
So whenever you're dealing with either an ExcelReference or calling
Excel via the XlCall.Excel interface, you are dealing with the C API.
Once place the ExcelReference type pops up, is if you want to receive
a sheet reference as one of the arguments to a function. Normally
you'd just get the value of the cell(s) as the argument passed in, so
if you actually want the reference, you should set your parameter to
by of type 'object' and add a special attribute,
[ExcelArgument(AllowReference=true)], to indicate that a reference
should be passed. With this attribute, function calls that are passed
a sheet reference will be called in your code with an object of type
ExcelReference, allowing you to make further calls to the C API with
this reference.

On the other hand, the COM Automation interface can be used from your
add-in as you would from VBA, taking the following into account:
* To get hold of the root Application object for the Excel instance
that is hosting your add-in, you should call the helper property
"ExcelDnaUtil.Application". Once you have the root Application object,
you can get hold of everything else from there.
* To use the COM Automation interface you either need to use the C# 4
'dynamic' support, or reference an interop assembly that declares the
COM types to .NET.
* The support for calling COM was much improved in C# 4, whereas older
versions you had to pass lots of "Missing" arguments, and properties
weren't easy to work with. To run .NET 4 in your Excel-DNA add-in, you
need to add a flag in the .dna file that sets RuntimeVersion="v4.0".
* I normally recommend that one avoids using the COM Automation
interface from within user-defined functions called from the
worksheet. I have no good evidence, but suspect this sometimes causes
issues. On the other hand, using the COM Automation interfaces
elsewhere, like macros, ribbon handlers etc from your Excel-DNA seems
to work perfectly.

Now to get to your problem:
Your code snippet is mixing the two Excel interfaces. Objects of type
"Range" are from the COM Automation interface, while the XlCall.Excel
call has to do with the C API.

If you have a reference to the interop assembly, which seems to be the
case from the "using" declaration in your snippet, you could call
Application.Intersect with code like this:

// Get the right Application COM object from Excel-DNA,
// and cast as a COM object of type Microsoft.Office.Interop.Excel
xl.Application app = (xl.Application)ExcelDnaUtil.Application;
// Get the range objects somehow
xl.Range r = ...
xl.Range s = ...
// work out the intersection
xl.Range rngIntersect = app.Intersect(r.EntireColumn, s.EntireColumn);



I hope this makes some sense, and look forward to your follow-up
questions.

Regards,
Govert