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 ExcelRangeObject. One issue that always trips me up is that the ExcelRangeObject 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 takesRangeObject and places its values into A. netArraylistObject, as follows:

Private function createarraylistfromrange _
(Byref range as Excel. Range )_
As arraylist

'Purpose: given an Excel range,
'Returns a one-dimen1_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 cocould call this function from a vsto event in an Excel solution as follows:

Private sub thisworkbook_open () handles thisworkbook. Open

'Using one-dimen1_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 toArraylistObject. However, to read these values out ofArraylistObject inThisworkbook_openEvent, I need to use a zero-based nomenclature. If I didn't subtract one from arraylist. Count I wocould get an out-of-bounds exception.

Another issue with this code snippet is thatArraylistObject 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 multidimenstmcell array. what I really want is to use the good oldArrayObject and make it mimic the multidimensponnature 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 multidimenrange
'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 multidimen=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.

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