Passing Ranges or Arrays to an ExcelDna COM server from VBA

[Harley Blumenfeld]

Hello All,

It's been a while since I have been on the forums but wanted to pick some brains out there. 

I have recently been tasked with fixing up and modifying a process that relied heavily on VBA to pull a bunch of data out of spread sheets, package them up as documents and send them off to some web services. The structure of the documents are fairly complicated so I liked the idea of creating classes that form a  DSL for building these things. 

I remember seeing Mikael Katajamäki's blog post about creating COM objects in C# and exposing them to VBA using Excel DNA:

http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna_16.html

Looking at the article it seemed a natural fit for my problem and also can help me to remove a lot of this VBA with .NET classes that I can easily version control, unit test and all the other benefits that you get with having your logic in an Excel-Dna add-in.

Enough background... on to my question....

Within the VBA there are sections of the code where arrays of data (doubles and dates mostly) are read from Ranges. What are people's recommendations of how to either pass this data in or have my program read from these ranges?

Here are my rough - unorganized thoughts so far:

• Ideally I'd like to pass typed arrays but Excel doesn't seem to like it when you have a function signature in your ComVisible object that takes an array of either object or DateTime, which makes sense since there is a bunch of Marshalling magic that needs to happen. 
• I could just pass the range or array as an object and then pull the values out using dynamic or reflection but was hoping for something a little more type safe. 
• I would like to avoid using Primary Interop Assemblies so that I am not tied to a specific version of Excel.

Any advice would be most appreciated.

-Harley

[Govert van Drimmelen]

Hi Harley,

I'd suggest you make the method accept an 'object' and then pass in the Range object from VBA. Use it via COM interop from the .NET side. The important rule is to only access the COM object model from the main thread.

You need not fear the version issues when using a Primary Interop Assembly. They don't tie you to a specific Excel version any more than VBA does.

* With .NET 4, you have the option to "Embed Interop Types", in the PIA references which you should switch on (or leave on, I think it's the default). Then you need not redistribute the PIA assemblies - the needed bits are compiled into your .dll.

* For different Excel versions, the situation is exactly the same as VBA: If you are using parts of the object model that available on the installation that you are running, it works. If you use COM types or methods that are only available on Excel 2013 and you run under Excel 2007, it gives an error. So if your VBA code runs on different Excel versions, the equivalent COM code in your Excel-DNA add-in will run on the same Excel versions.

-Govert