how to pass variant array to C# using ExcelDNA

Question

If I have a c# method like this:

    [ExcelFunction(IsMacroType = true)]
    public static void TestMacro([ExcelArgument(AllowReference = true)]object rngTable, [ExcelArgument(AllowReference = true)]object varCalc)
    {
        System.Windows.Forms.MessageBox.Show("TestMacro called!");
    }

I can successfully call it from VBA using this code:

Sub TestIt()
    Application.Run "TestMacro", Range("a1"), Range("a2")
End Sub

but calling it from this code:

Sub TestIt2()
    Dim v(1 To 1) As Variant
    Set v(1) = Range("a2")
    Application.Run "TestMacro", Range("a1"), v
End Sub

Generates a "type mismatch" error, and the TestMacro method is not called. Within VBA I can write Subs to accept a "Variant" of this type. How do I do so with ExcelDNA?

Answer 1

The Excel C API (which Excel-DNA uses to expose functions and macros) does not support sheet references inside array parameters.

If your primary aim is to make an add-in that you can easily call from VBA, you can use the COM server feature of Excel-DNA to expose a class library, that you call from VBA via Tools->References and making new objects etc. Then the integration does not go through the Excel C API but through the COM interfaces directly.

There's nice step-by-step sample of how to do that here: https://github.com/Excel-DNA/Samples/tree/master/DnaComServer

If you want to stay with a simple macro that you call via Application.Run, and want to pass in arrays that contain sheet references, you might need to pass the address in or something like that.