Arrays and Visual Studio Tools for office

Source: Internet
Author: User
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.