VB6 to VB.NET, 1 based Arrays and Excel's Range.Value

I'm converting a VB6 based Excel COM addin to VB.NET using Visual Studio 2008.

I am trying to understand how best to deal with the differences in array handling between VB6 and VB.NET when it comes to using Excel's Range.Value property.

My main problem is that I'm troubled with the fact that when I'm reading in ranges of cells (ArrayVariable = Range.Value), I'm dealing with 1 based subscripts (see scenario 1 below) and when I'm assigning arrays created in code to Range.Value, I'm dealing with 0 based subscripts. (scenario 2)

It seems to me that I should be dealing with either 0 or 1 based subscripts and not both.

Is there something fundamental I am missing about VB.NET, arrays and Excel's Range.Value?

Is there a method that I can call in place of Range.Value to return a 0 based array? Should I settle on 0 based arrays and convert the 1 based arrays received from Range.Value to 0 based ones? This seems like an expensive (processor + memory) option given that I could have some ranges that are over 50,000 rows and 200 columns.

Any suggestions would be appreciated.



There are two main scenarios that crop up all over the place in the code I am converting:

Scenario 1: Loading a range of cells from a sheet into an array, processing them and then storing them back to the sheet.

In this scenario, the VB.NET code can continue to use 1 based array subscripts so there's not much to change when coverting to VB.NET.

VB6
Dim Cells  'as Variant implied
Cells = Range.Value 'Range has been set to "A1:E3"
For r = 1 to 3
   For c = 1 to 5
      ' do something to Cells (r,c)
   next c
next r
' save cells back to sheet
Range.Value = Cells

VB.NET
Dim Cells As Object = Range.Value 'Range has been set to "A1:E3"
For r = 1 to 3 'Cells.GetUpperBound(0) returns 3
   For c = 1 to 5 'Cells.GetUpperBound(1) returns 5
      ' do something to Cells (r,c)
   next c
next r
' save cells back to sheet
Range.Value = Cells


Scenario 2: (Saving a VB created array to a range of cells in a sheet.)

VB6
Dim Cells(1 To 3, 1 To 5) ' as variant implied
For r = 1 To 3
  For c = 1 To 5
    Cells(r,c) = "Something"
  Next c
Next r
Range.Value = Cells 'Range has been set to "A1:E3"

VB.NET
Dim Cells(0 To 2, 0 To 4) as Object
For r = 0 To 2
  For c = 0 To 4
    Cells(r,c) = "Something"
  Next c
Next r
Range.Value = Cells 'Range has been set to "A1:E3"

In this case I am forced to create 0 based arrays in VB.NET and so much of the VB6 code has to change.

This in itself is no big deal but it could get nasty when I need to work with both a 1 based array returned from Range.Value and a 0 based array created in code.

Again, any help would be appreciated.
ou81aswellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G0ggyCommented:
I apologise that this does not specifically answer the question you are asking, but I do some work with very large spreadsheets and one things I have now found it is far easier to convert them to comma delimited files and work on them that way.

Aside from the obvious ease in row select and column selection, the memory overhead saved is enormous.

Hope this helps in some way.
0
ElrondCTCommented:
VB is actually structured the way all programming languages are, at least at the fundamental level; some languages let you renumber, but they're internally translating back to 0-based. Excel is the odd man out, because it's designed for laypeople rather than initiates (and what's the letter equivalent of zero for columns?).

However, it's not quite as confusing as you're making it. When you pull a range into a VB array with

Cells = Range.Value

the first cell of Range is put into Cells(0, 0), NOT Cells(1, 1). So you only do 1-based references when you're actually referencing Excel cells, not when accessing a memory array that represents the Excel cells. You just have to remember that the count is based differently in the two locations, so Cells(5, 5) is the same as Range("F6"); it's a nuisance, but that's the way it is.
0
ou81aswellAuthor Commented:
This is from my Immediate window after Cells = Range.Value has been executed. Range hase been set to A2:B3
? Cells
{System.Array}
    (1,1): 20000.0 {Double}
    (1,2): #1/1/2008# {Date}
    (2,1): 20001.0 {Double}
    (2,2): #1/1/2008# {Date}
? Cells(0,0)
Run-time exception thrown : System.IndexOutOfRangeException - Index was outside the bounds of the array.
? Cells.GetLowerBound(0)
1 {Integer}
    Integer: 1 {Integer}

As you can see the array is 1 based. This is confusing me to no end because everything I have read suggests that all arrays in VB.NET are 0 based.
0
ou81aswellAuthor Commented:
After some more head scratching I discovered that 1 based arrays are indeed 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 use 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.

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)

Here is some output from the immediate window after the statement Dim myArray = xlCreateRangeArray(GetType(Object), 2, 3) has executed:

myArray(1,1) = "This is a test"
? myArray
{System.Array}
    (1,1): "This is a test" {String}
    (1,2): Nothing
    (1,3): Nothing
    (2,1): Nothing
    (2,2): Nothing
    (2,3): Nothing

And I can move that array back to Excel with:

Range.Value = myArray

Are there any significant performance penalties I might be facing using this technique? I'm hoping the answer is no because VB.NET's native arrays are based on the System.Array.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ElrondCTCommented:
If I'd thought about it more, I would have realized that there must be a way to create arrays with a lower bound greater than zero; otherwise, why have the .GetLowerBound property? I'm glad you found it. I'm surprised that you got a different result viewing the array created by Cells = Range.Value than I did; I'm not sure what to make of that.

Playing around a bit with Reflector to see how this kind of array differs from a regular array, it looks like there would be at least a small performance penalty. This structure of array uses late binding, which requires a bit more overhead at runtime. Whether it's significant would depend on how much data massaging you're going to do. If you'll be working with dozens or hundreds of cells, I think the difference will be lost in the rounding. If you're going to be working with tens of thousands of cells at a time, the difference might become noticeable. You'd have to run tests both ways to know for sure what the impact is.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.