A developer on my team was recently trying to set the contents of a small column of excel cells to an array of values.  This is a desirable thing to do—if you are setting a range of values in Excel, it is much better to create an array and make one call rather than set the values a cell at a time.  However, his code was yielding unexpected results.

 

First he wrote this C# code:

 

     // <DOESNT WORK>

     Excel.Range r = this.Range["A2", "A4"];

     object[] values = new object[3] { 2, 3, 4 };

     r.Value2 = values;

     // </DOESNT WORK>

 

Although this code seems reasonable, what it ended up doing was setting the entire column area (column A from row 2 to row 4 inclusive) to the value “2”

 

What was he doing wrong?  Read on for the long answer which includes some low level COM details—although the quick answer is in this KB article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;302094

 

Looking at what was happening at the interop and COM level, this code was resulting in a VARIANT being passed to the Value2 property of Excel.  The VARIANT created by COM Interop had a type of VT_ARRAY | VT_VARIANT.  So far so good, we’re passing an array of variants which makes sense.  When a VARIANT’s VARTYPE vt field is set to VT_ARRAY you next want to look at the parray member of the VARIANT (a pointer to a SAFEARRAY) field to examine the SAFEARRAY structure where the array is placed.  This structure looked like this:

 

parray

            cDims = 1 (count of dimensions in this array)

            rgsabound[0]

                        cElements = 3 (number of elements in dimension 1)

 

So we were passing a one dimensional array to Excel.  Excel was just grabbing the first element (which was “2”) and setting the whole column to 2 which is a bit confusing--it would be nice if it raised an error or something, but it doesn't.

 

After some experimentation, he arrived at some code that worked:

 

     // <WORKS!!>

     Excel.Range r = this.Range["B2", "B4"];

     object[,] workingValues = new object[3, 1];

     for (int i = 0; i < 3; i++)

     {

          workingValues[i, 0] = i + 2;  // 2,3,4

 

     }

 

     r.Value2 = workingValues;

// </WORKS>

 

Looking at the result of this code, the VARIANT created by Interop had a type of VT_ARRAY | VT_VARIANT just like the first example.  The SAFEARRAY pointer parray looked like this:

 

parray

            cDims 2

            rgsabound[0]

                        cElements 1

            rgsabound[1]

                        cElements 3

 

So now he was passing a two dimensional array to Excel. Note that the array rgsabound is stored with the left-most dimension of our C# array (3) in rgsabound[cDims -1] and the right-most dimension of our C# array (1) in rgsabound[0].  This is the opposite of the behavior described for C in the VARIANT automation documentation: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/automat/htm/chap7_9ntx.asp

 

Finally, I wrote some VBA code to do the same thing as what was being done in C# to verify that the resulting created VARIANT was the same:

 

Public Sub test()

    Dim r As Range

    Set r = Me.Range("C2", "C4")

    Dim a(0 To 2, 0 To 0) As Variant

   

    Dim v1 As Variant

    v1 = "2"

    Dim v2 As Variant

    v2 = "3"

    Dim c1 As Variant

    v3 = "4"

   

    a(0, 0) = v1

    a(1, 0) = v2

    a(2, 0) = v3

   

    r.Value2 = a

End Sub

 

This code generated a VARIANT that was identical to the one described in the C# example that “Works”

 

So the long winded moral of the story (which is mainly an excuse to talk about a little of the inner workings that are going on in COM interop when you write this code) is that when you want to set a range of values to an array, you must declare that array as a 2 dimensional array where the left-most dimension is the number of rows you are going to set and the right-most dimension is the number of columns you are going to set.  Even if you are just setting one column, you can’t create a 1 dimensional array and have it work.