VB6 Excel Com Addin to VB.NET: Range.Value and VB Arrays

Add-in Express™ Support Service
That's what is more important than anything else

VB6 Excel Com Addin to VB.NET: Range.Value and VB Arrays
 
John West




Posts: 37
 Joined: 2010-02-15
I'm using VS 2008 / VB.NET / ADX 2009

When I read a range into an array with the following VB.NET code, the array returned is 1 based:

Cells = Range.Value
' do something to Cells(,)
Range.Value = Cells

When I write an array that I created in VB, to a range, the array is 0 based:

Dim myCells(r,n) as Object
' fill in values of myCells(,)
Range.Value = myCells

In VB6 I would Dim myCells(1 to nRows, 1 to nColumns), fill them in and then do a Range.Value = myCells so the arrays I worked with were 1 based when I wrote them and when I read them.

What's the best way to work in one base only? Should I convert the base of my arrays from one to the other and if so, how can this be done efficiently given that a range may occupy tens of thousands of rows and hundreds of columns?

The addin is set to target Excel 2000 and up but my actual requirement is for it to work with Excel 2007 or even Excel 2010 and up if it makes any difference.

Any suggestions or pointers would be appreciated.

Thanks.
Posted 23 Mar, 2010 21:59:41 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hi John,

I've just tested the following code with version-neutral interops: 

private void adxRibbonButton1_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
    Excel.Worksheet worksheet = ExcelApp.ActiveSheet as Excel.Worksheet;
    Excel.Range range = worksheet.get_Range("A1", "B5");

    object[,] values = new object[5, 2];
    values[0, 0] = 11; values[0, 1] = 21;
    values[1, 0] = 12; values[1, 1] = 22;
    values[2, 0] = 13; values[2, 1] = 23;
    values[3, 0] = 14; values[3, 1] = 24;
    values[4, 0] = 15; values[4, 1] = 25;

    range.Value = values;
}



Andrei Smolin
Add-in Express Team Leader
Posted 24 Mar, 2010 05:31:02 Top
John West




Posts: 37
 Joined: 2010-02-15
Sorry but I don't understand your point (loading a range from an array requires a 0 based array as I have found) plus I'm using VB.net

My problem is that doing a Cells = Range.Value results in a one based array and I would prefer to work with a single base (0).

Thanks.
Posted 24 Mar, 2010 06:02:10 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hi John,

John West writes:
My problem is that doing a Cells = Range.Value results in a one based array and I would prefer to work with a single base (0).


This is how Office works and I don't see a way to change this behavior.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Mar, 2010 08:58:00 Top
John West




Posts: 37
 Joined: 2010-02-15
Thanks again Andrei. I realize that this is not an issue with ADX and appreciate your insight.

Everything I have read makes it clear that in VB.NET all arrays are 0 based. Then along comes Excel's Cells = Range.Value and I get a 1 based array in a language that I thought doesn't allow 1 based arrays. I need to study VB.NET arrays more because I'm obviously missing something fundamental here like what the relationships between VB arrays and System.Arrays are.

Again, in VB6 this was not a problem because I could force all of my arrays to be one based (by Dim'ing them rather than using Option Base) and not worry how the array was created.

It's not easy being stupid. I have to get up early and practise every day! :)
Posted 24 Mar, 2010 13:41:31 Top
John West




Posts: 37
 Joined: 2010-02-15
After some more head scratching I discovered that 1 based arrays are possible in VB.NET.

They have to be created using the Array.CreateInstance function which lets you specify the dimensions as well as the lower bounds for each rank.

I created the following function to make the process as simple as using a Dim statement. Now I don't have to change the VB6 code that was using 1 based arrays (and there's lots of it all over the place). 

Public Function xlCreateRangeArray(ByVal arrayType As System.Type, ByVal nRows As Integer, ByVal nCols As Integer) As Array
        Dim Dimensions() As Integer = {nRows, nCols}
        Dim LowerBounds() As Integer = {1, 1}
        xlCreateRangeArray = Array.CreateInstance(arrayType, Dimensions, LowerBounds)
End Function


I can then create 1 based arrays in code as follows: 

Dim myArray = xlCreateRangeArray(GetType(Object), 2, 3)


So my old VB6 code that reads 

Dim myArray(1 to 2, 1 to 3)
Dim myStringArray(1 to 2, 1 to 3) as String


becomes 

Dim myArray = xlCreateRangeArray(GetType(Object), 2, 3)
Dim myStringArray = xlCreateRangeArray(GetType(String), 2, 3)


This seems to be the solution I am looking for. I hope there are no performance penalties that I am missing.
Posted 25 Mar, 2010 05:51:09 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Thanks a lot, John, for posting this here!


Andrei Smolin
Add-in Express Team Leader
Posted 25 Mar, 2010 07:15:22 Top