Fun with Excel--setting a range of cells via an array

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:;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:



            cDims = 1 (count of dimensions in this array)


                        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:



            cDims 2


                        cElements 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:


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.