Array subscripts in VBA- Can anyone explain this?

Question

Please find attached a screenshot of a small test program that I wrote to illustrate a confusion I am having. The text in <> is inputted by me to explain the error I am getting.

The MsgBox was created to make the program stay in a break mode so that I can see what the values were.

Here is the code. (I had discarded the earlier version but the values should still be same)

Sub test()
Dim Test1()
Dim Test2()

'values hardcoded in the sheet from where this macro is launched
'Cells(7,4) = 1
'Cells(7,5) = 2
'Cells(7,6) = 3
'Cells(7,7) = 4
'Cells(7,8) = 5
'Cells(7,9) = 6
'Cells(7,10) = 7


Set Rng1 = Range(Cells(7, 4), Cells(7, 10))
Test1 = Rng1.Value2
ReDim Test2(4)
Test2 = Rng1.Value2
MsgBox ("This was a test program")

End Sub

My Questions

1. It is a single row array - so why does Excel take it as a two dimensional array?
2. Normally the first row and column start from 0 i.e. zero. So why do I have to use 1 for accessing the values picked by the array? Or why does row 0 and column 0 give an error?
3. Even if Redim statement forces it to have 4 values, why is it picking up the fifth value? Does this mean we need not worry about Redimensioning the array if more values are likely to be encountered and Excel will always do the needful?

Thanks a lot in advance.

Answer 1

If you get an array from a range, it will almost always be a two-dimensional array. The exception to this is a range that is a single cell.

EDIT#1:

To verify the Ubounds and Lbounds, try running MAIN :

Sub SizeAndShape(ary)
    MsgBox LBound(ary, 1) & "-" & UBound(ary, 1) & vbCrLf & LBound(ary, 2) & "-" & UBound(ary, 2)
End Sub

Sub MAIN()
    Set Rng1 = Range(Cells(7, 4), Cells(7, 10))
    Test1 = Rng1.Value2
    Call SizeAndShape(Test1)
End Sub

Answer 2

It looks like you are simply overwriting your array with the range you set as Rng1. After the code of

Test2 = Rng1.Value2

This means that your array is not the array you ReDimed, but the array of values returned by Excel's Value2 property, which is a 1 based 2D array, one value for each cell in Rng1.

So, yes, arrays, unless specified, are 0 based, but the array returned by the Value2 property is 1 based when returned for a range of values. (If you call Value2 on a single cell instead of an range of cells, you just get a Variant.)