Dealing With Excel Arrays

One of the nice things in Excel is the ability to treat scalar, any size vector, and any size matrix in the same way (from the developer's perspective). This is very powerful, and saves a lot of development time.

It gets interesting when you add COM and C# into the picture… I am working on a demo which includes Excel and C# (more on the demo in a different post), and one of the things we needed to do is pass arrays from the spreadsheet to our C# logic. We are using COM for the interaction. This is where the problem starts. When you fill up an array in Excel and pass it over COM, it is received at the other side as an object, not as an array, and you can not access the array's elements.

The solution for that is to use reflection and to interrogate the object using simple array methods. It took us some time to find how this should be done properly (thanks to PK), and this is it:

First of all, let's assume we have a range of doubles in your spreadsheet. I named the range "double_range":





Next, I have a utility function which populates a VBA array with the values:





My VBA code which passes the array, looks like:



My C# object does not use an attribute setter, but rather have a setter method which invokes a translator utility function:



Translator function is the most important piece of this puzzle:



I have uploaded the code snippets into a text file here.

The VBSpaceAPI will include such helpers in its next release and Will support arrays natively.

-Guy