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

Posted on 2010-03-23
Medium Priority
Last Modified: 2013-11-10
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.

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

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.)

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"

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.
Question by:ou81aswell
  • 2
  • 2
LVL 11

Expert Comment

ID: 28441794
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.
LVL 20

Expert Comment

ID: 28456580
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.

Author Comment

ID: 28528034
This is from my Immediate window after Cells = Range.Value has been executed. Range hase been set to A2:B3
? Cells
    (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.

Accepted Solution

ou81aswell earned 0 total points
ID: 28538268
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


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
    (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.
LVL 20

Expert Comment

ID: 28563762
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.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Make the most of your online learning experience.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Loops Section Overview

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question