Freigeben über

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

  • Artikel

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: https://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: https://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

  • Anonymous
    May 04, 2004
    The comment has been removed
  • Anonymous
    May 05, 2004
    Good information, I hadn't tried a 1D array with rows--I can't find the "Application.Transpose" method you refer to though.
  • Anonymous
    May 08, 2004
    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.
  • Anonymous
    May 08, 2004
    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[,]
  • Anonymous
    July 14, 2004
    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()
  • Anonymous
    March 20, 2008
    PingBack from http://cityjokesblog.info/net4office-fun-with-excel-setting-a-range-of-cells-via-an-array/