Reading an Excel range into Object(,) - 1 based?

Arthlan

Hi,

I've been using something I learned (probably here), but don't really understand what's going on.

I read ranges into a variable with the following:

Dim mValues As Object(,) = _
CType(Globals.Sheet1.Range(Cell(r1, c1), Cell(r2,c2)).Value, Object(,))

Where I use "Cell" to save typing:
Private Shared Function Cell(ByVal Row As Integer, ByVal Column As Integer) _
As Excel.Range
Return CType(Globals.sheet1.Cells.Item(Row, Column), Excel.Range)
End Function

This works tremendously faster than reading in values cell by cell. When I do this, however, mValues becomes a 1-based array. First, I don't understand what's going on that would make that happen. Second, I didn't think you could have 1-based arrays!

Can anyone give me some insight as to what's going on here

-------------

Some related questions --

Can I change this 1-based array into a 0-based array without the overhead of copying each element from one array to the other

It would often be convenient to have a 1-dim array containing a row of the 2-dim array. Is there a fast way to make this assignment That is, is there something that would be faster than copying each element for some row over Or perhaps is there a way to pass one row of the 2-dim array as an argument

I'd appreciate any insight or suggestions.

Thanks

Cindy Meister

Hi Arthlan

1-based arrays are a classic VB-animal. Since the "native" Office development language is VBA, the Office object models tend to use such things. I imagine the Excel development team set it up this way to make it simpler to match up results with the cells (so you don't need to subtract one from the "counter" to figure out which cell).

VB.NET appears to support 1-based arrays to a limited extent, probably to support just such constructions in legacy projects. See, for example

http://support.microsoft.com/kb/818215/en-us

So no, there's no simple (efficient) way for you to convert a 1-based array to a zero-based array. I also don't know of any way to extract all the items of a single dimension of an array, although creating a collection from the array might be a possibility. The VB.NET Language forum would be a better place to pursue these kinds of questions.

Dennis Wallentin

Hi Art,

Funny, the genuine VBA purist Excel developers complain to a high degree that .NET use 0-based arrays

To simplify it:
- When using an Object array (or in VBA terms a Variant array) they are per design 1-based.
- There is no need to convert them to 0-based arrays (i e no need to create overhead).
- The Object arrays are usually used when working with ranges and other objects in Excel and these objects starts at 1 and not 0.

As an Excel developer I find them both powerful and useful but as always we need to consider when to use them and when not.

Arthlan

Cindy,

Thanks for the insight. I haven't yet looked at the links you provided, but will shortly.

Thanks again.

Arthlan

Dennis,

Thanks for the info. One thing, though. When I create an object array directly, it seems to be 0-based. It doesn't become 1-based until I read in an Excel range.

Thanks!

Dennis Wallentin

Art,

As long as the code don't interact with Excel, that is reading values from ranges, the Object array is treated as a .NET 0-based array.

0-based: 

Dim Arr(,) As Object = {{1, 2, 3}, {4, 5, 6}}

MessageBox.Show(CStr(LBound(Arr)))

Me.Range("A1:B3").Value = Application.WorksheetFunction.Transpose(Arr)

1-based:

Dim Source(,) As Object

Source = CType(Me.Range("A1:B3").Value, [Object](,))

MessageBox.Show(CStr(LBound(Source)))