Arrays and Visual Studio Tools for Office

Arrays and Visual Studio Tools for Office

When developing solutions using Microsoft Visual Studio Tools for the Microsoft Office System, I (Paul Cornell) find it much easier to pass around intrinsic .NET object instead of Microsoft Office interop objects. Case in point: the Microsoft Office Excel Range object. One issue that always trips me up is that the Excel Range object is one-based, and .NET arrays are generally zero-based. There's nothing in my opinion that makes working with zero-based arrays any better than one-based arrays; it's just that .NET relies very heavily on zero-based arrays, and it's something I always need to be aware of when I'm developing Visual Studio Tools for Office solutions. To demonstrate, last night I wrote a helper function that takes a Range object and places its values into a .NET ArrayList object, as follows:

Private Function CreateArrayListFromRange _
    (ByRef Range As Excel.Range) _
    As ArrayList

    ' Purpose: Given an Excel range,
    ' returns a one-dimensional array of the range's values.

    Dim arrayList As New ArrayList

    For row As Integer = 1 To Range.Rows.Count
        For column As Integer = 1 To Range.Columns.Count
            arrayList.Add(CType(Range(row, column), _
                Excel.Range).Value2)
        Next column
    Next row

    Return arrayList

End Function

You could call this function from a VSTO event in an Excel solution as follows:

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open

    ' Using one-dimensional array list.
    ' Assumes you have a worksheet named "Sheet1" and a list
    ' of values in the A2:C4 range on Sheet1.

    Try

        Dim arrayList As New ArrayList

        arrayList = CreateArrayListFromRange _
            (CType(ThisWorkbook.Worksheets("Sheet1"), _
            Excel.Worksheet).Range("A2", "C4"))

        For item As Integer = 0 To arrayList.Count - 1
            MsgBox(arrayList.Item(item) & " (item " & item & ")")
        Next item

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

End Sub

Note that in the CreateArrayListFromRange function, I can simply iterate through the values using a one-based nomenclature and write the values to the ArrayList object. However, to read these values out of the ArrayList object in the ThisWorkbook_Open event, I need to use a zero-based nomenclature. If I didn't subtract one from arrayList.Count I would get an out-of-bounds exception.

Another issue with this code snippet is that an ArrayList object works fine if you're only concerned about a single row or single column of cell values. This code doesn't work well for a multidimensional cell array. What I really want is to use the good old Array object and make it mimic the multidimensional nature of a deeper range of cells. Here's how this morning I modified my earlier code to do this:

Private Function CreateArrayFromRange _
    (ByRef Range As Excel.Range, _
    ByVal rows As Integer, ByVal columns As Integer) As Array

    ' Purpose: Given an Excel range, returns a multidimensional
    ' array of the range's values.

    Dim array As Array = array.CreateInstance _
        (GetType(Object), rows, columns)

    For row As Integer = 1 To rows
        For column As Integer = 1 To columns
            array.SetValue((CType(Range(row, column), _
                Excel.Range).Value2), row - 1, column - 1)
        Next column
    Next row

    Return array

End Function

Now I can call this function as follows:

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open

    ' Using multidimensional array list.
    ' Assumes you have a worksheet named "Sheet1" and a list
    ' of values in the A2:C4 range on Sheet1.

    Try

        Dim range As Excel.Range = _
            CType(ThisWorkbook.Worksheets("Sheet1"), _
            Excel.Worksheet).Range("A2", "C4")

        Dim array As Array = _
            CreateArrayFromRange(range, range.Rows.Count, _
            range.Columns.Count)

        For row As Integer = 0 To range.Rows.Count - 1
            For column As Integer = 0 To range.Columns.Count - 1
                MsgBox(array.GetValue(row, column) & _
                    " (item " & row & ", " & column & ")")
            Next column
        Next row

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

End Sub

Notice that I have to be even more careful when I deal with one-based arrays and zero-based arrays, especially when it comes to the array.SetValue call in the CreateArrayFromRange function.

詳：http://blogs.msdn.com/vsto/archive/2003/11/25/39744.aspx