Inspecting elements of a range

[tiredofit]

If I have the value 1,2,3,4,5 in cells A1 to E1 and I write:

Dim DataArray() As Variant

DataArray() = Range("A1:E1").CurrentRegion.Value

I can inspect the elements in the Locals window easily.

If instead of assigning an array to the data, I use a range, as follows:

Dim Rng As Range
Set Rng = Range("A1:E1")

when I look at the Locals window, there is a lot more info .Where can I see the range's elements?

I have also notice typing:

? Rng(2).Value

and

? Rng(1,2).Value

returns the same value of 2.

Why is that?

Thanks

 

[6StringJazzer]

when I look at the Locals window, there is a lot more info .Where can I see the range's elements?

There is a lot more info because Range is an object and you are seeing all of the properties of that object. Unfortunately it does not show individual elements. Those are a lower-level property and you have to query them individually.

I have also notice typing:

? Rng(2).Value

and

? Rng(1,2).Value

returns the same value of 2.

Why is that?

I am assuming that cell B1 contains the value 2. When you address a range with two arguments (1, 2) it is a (row, column) reference. So in the range A1:E1 it refers to the first row, second column which is B1. When you address a range with one argument (2), it acts more like an array, laid out by row. In your case elements 1-5 are in the first row (you only have one row anyway), so you get element 2 which is also B1.

If your range were A1:E3 then (2, 1) would address A2. The address format (8) would address the 8th element; 1-5 are in the first row, so starting the second row with element 6, so element 8 is C2.

 

[tiredofit]

There is a lot more info because Range is an object and you are seeing all of the properties of that object. Unfortunately it does not show individual elements. Those are a lower-level property and you have to query them individually.


I am assuming that cell B1 contains the value 2. When you address a range with two arguments (1, 2) it is a (row, column) reference. So in the range A1:E1 it refers to the first row, second column which is B1. When you address a range with one argument (2), it acts more like an array, laid out by row. In your case elements 1-5 are in the first row (you only have one row anyway), so you get element 2 which is also B1.

If your range were A1:E3 then (2, 1) would address A2. The address format (8) would address the 8th element; 1-5 are in the first row, so starting the second row with element 6, so element 8 is C2.

Many thanks for your detailed explanation.

What I find particularly strange is writing:

? Rng(6).Value

returns an empty string whereas if it were an array, there would be a subscript out of range error message.

 

[Domenic]

when I look at the Locals window, there is a lot more info .Where can I see the range's elements?

When you look under Rng, you see all of the properties listed. If you scroll all the way down to Value2 and click on the plus (+) sign to expand it, you'll see the array of values.

 

[tiredofit]

When you look under Rng, you see all of the properties listed. If you scroll all the way down to Value2 and click on the plus (+) sign to expand it, you'll see the array of values.

Thanks for clarifying.

 

[6StringJazzer]

When you look under Rng, you see all of the properties listed. If you scroll all the way down to Value2 and click on the plus (+) sign to expand it, you'll see the array of values.

Thank you for that, I missed it.

 

[6StringJazzer]

What I find particularly strange is writing:

? Rng(6).Value

returns an empty string whereas if it were an array, there would be a subscript out of range error message.

Yes, it's not really an array, it just uses similar syntax. In Excel VBA you can reference past the end of a range and it will be treated as if the range just includes more rows. It returns an empty string because the cell is empty. In your case Rng(6) will refer to A2 even though it's not in the range. You could also refer to Rng(50, 50) and it uses the indices based on the upper-left cell of the range, without regard to how large the range actually is.