Passing an Excel.Range from VBA to C# via Excel-DNA

Question

I'm working on figuring out how the proper/best way to pass Excel.Range objects from VBA in Excel to C# via Excel-DNA, and then interacting with the Excel object model via that passed reference.

Sample code:

    '***************** VBA code ********************
    Sub test2()
        Dim rng As Excel.Range
        Set rng = Worksheets("test_output").Range("D9")

        Dim myDLL As New XLServer.MyClass
        Call myDLL.test_ExcelRangePassedAsObject(rng)
        Call myDLL.test_ExcelRangePassedAsRange(rng)
    End Sub



//**************** C# Code **********************
using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

namespace XLServer
{
    [ComVisible(false)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }    

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class MyClass                            
    {
        public void test_ExcelRangePassedAsObject(object range)
        {
            Excel.Range rng = (Excel.Range)range;
            rng.Worksheet.Cells[1, 1].Value = "Specified range was: " + rng.Address.ToString();
        }

        public void test_ExcelRangePassedAsRange(Excel.Range range)
        {
            Excel.Range rng = range;
            rng.Worksheet.Cells[2, 1].Value = "Specified range was: " + rng.Address.ToString();
        }

And when I run this, I get the expected output:
A1: Specified range was: $D$9
A2: Specified range was: $D$9

After spending a significant amount of time googling Excel-DNA questions and coming up with nothing, I just wrote this and was actually surprised that it worked with no issues.

So I guess my question is to those who are experienced in this...is this the proper/best way to go about it?

For example, I came across many posts similar to this:
https://groups.google.com/forum/#!topic/exceldna/zqzEIos7ma0

....where people were dealing with ExcelReference objects and having to use incredibly obscure code to convert those into Excel.Range objects and it almost feels like there's something I'm missing?

Answer 1

Yes, it's the right way to do it, as long as you:

1. are sure your library project is not marked as "Register for COM interop". You don't want to build process to directly register your .dll as the COM server for your XLServer.MyClass COM type, but rather have the .xll serve as the COM server. That will ensure your COM objects live in the same AppDomain as the rest of the add-in.

2. add the ComServer attribute to the <ExternalLibrary> tag in the .dna file, thus as

   <ExternalLibrary Path="XlServer.dll" ComServer="true" ... />.

3. take care of all the COM interface versioning rules.

The best references on the topic are the walk-through articles by Mikael Katajamäki, which I presume you found:

• http://mikejuniperhill.blogspot.fi/2014/03/interfacing-c-and-vba-with-exceldna-no.html
• http://mikejuniperhill.blogspot.fi/2014/03/interfacing-c-and-vba-with-exceldna_16.html

---

Now for some background to give some context to the Google group discussion:

Most of the Excel-DNA internals is concerned with the Excel C API (as defined by the Excel SDK) which is great for making high-performance worksheet UDFs. In this setting, the C API structure that describes a worksheet reference is wrapper in the ExcelDna.Integration.ExcelReference type. This is a small data structure that wraps a sheet pointer (called the SheetId), and the extents of the reference. ExcelReference objects can be safely used in multi-threaded UDFs, and can be passed back-and-forth to the C API. Excel-DNA worksheet functions can be configured to receive ExcelReference objects as input, when appropriate.

The Range COM object is quite a different thing. It is a COM object wrapper around such a sheet reference, subject to all the COM threading, lifetime and other restrictions. Microsoft does not support using the COM object model (and hence the Range objects) inside UDF functions defined in an .xll, and while it mostly works, trying to use these in multi-threaded functions is likely to cause serious trouble.

Inside a macro or ribbon callback (as async triggered macro that is run started by ExcelAsyncUtil.QueueAsMacro) that you put in an Excel-DNA add-in, you can safely use both the C API and the COM object model.

Converting from an thin ExcelReference object to a COM Range object is mot so hard, though it depends on whether you want to support multi-area ranges. If not, it's as simple as:

static Range ReferenceToRange(ExcelReference xlref)
{
    string refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
    dynamic app = ExcelDnaUtil.Application;
    return app.Range[refText];
} 

The only reason this helper is not part of the ExcelReference type is that Excel-DNA still support .NET 2.0, and the COM type unification that would make sense of this is only available since .NET 4.0