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

Comments (6)

  1. Mike Woodhouse says:

    This has been a problem since time immemorial (well, since the advent of VBA in Excel, anyway). Excel is just a tad stupid when a range is given a one-dimensional array: it always puts the values *across* the row, not down the column.

    So this:

    Excel.Range r = this.Range["A2", "C2"]; // note the change of the second addess

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

    r.Value2 = values;

    …ought to work.

    I don’t know about VSTO, but to put a 1-D array down a column in VBA generally requires something like this:

    r.Value2 = Application.Transpose(a)

    …or an explicit transposition to a n-by-1 array.

    It seems a shame that Excel doesn’t spot the 1-D to 1-D correspondence and handle the request accordingly, but perhaps there’s some historic reason for it. Of course, changing the behaviour now might well break vast quantities of existing code…

  2. Eric Carter says:

    Good information, I hadn’t tried a 1D array with rows–I can’t find the "Application.Transpose" method you refer to though.

  3. Jim Smith says:

    The following code works with single dimension arrays. I will typically use it for setting column headings.

    object[] ar = new object[8]{"This","Is","A","Test","Of","Single","Dimension","Arrays"};

    ((Excel.Worksheet)ThisWorkbook.Worksheets["Test"]).get_Range("D2",Type.Missing).get_Resize(1,8).Value2 = ar;

    For two-dimensional arrays, I use something like:

    ((Excel.Range)MyWorksheet.Cells[4,1]).get_Resize(

    ar.GetLength(0),

    ar.GetLength(1)).Value2 = ar;

    The get_Resize method is the key to making it work.

  4. Jim Smith says:

    Note on my previous post that for the two dimensional example the array object has to be defined as object[,] ar = some method returning an object[,]

  5. Eric Feeny says:

    good call on the 1D array transpose.

    now it works just the same as the 2D one.

    and for Eric Carter the method exists in:

    Application.WorksheetFunctions.Transpose()