Array error tuning out of range - it’s not clear why?

Question

I declared the array as such Dim rArray() As Variant , but when I try to use the values ​​that are stored in it (as shown below), I get an index error. UBound(rArray) and LBound(rArray) both return 14 and 1, but an error occurs on the Debug.Print line.

If I use the for statement as below

 For Each rArr in rArray 

then it works without problems, but for the purposes that I create for this array, I need the flexibility to select each element stored in this order, that is, I need to reference them using indexes.

I tried several ways to try and solve it without any luck and spend almost half my day on this problem. Can anyone point out what I need to change to make this work.

 Set rng = Range("D4", Range("D4").End(xlDown)) rng.NumberFormat = "0" rArray = rng.Value For x = UBound(rArray) To LBound(rArray) Step -1 Debug.Print rArray(x) Next x 

Edit: Another fact worth mentioning is that it is declared and used inside the function, but not passed from or to the function. Is it impossible to declare and use arrays in functions?

Answer 1

When you assign values ​​for a sheet to an alternative array, you always get a two-dimensional array based on 1 (for example, 1 for something, 1 for something, never 0 for something, 0 for something). If you get values ​​from one column, the second rank is only 1 to 1.

This can be proved as follows.

 Dim x As Long, rArray As Variant, rng As Range Set rng = Range("D4", Range("D4").End(xlDown)) rng.NumberFormat = "0" 'don't really understand why this is here rArray = rng.Value Debug.Print LBound(rArray, 1) & ":" & UBound(rArray, 1) Debug.Print LBound(rArray, 2) & ":" & UBound(rArray, 2) For x = UBound(rArray, 1) To LBound(rArray, 1) Step -1 Debug.Print rArray(x, 1) Next x 

So, you need to ask for the element in the first rank of the array; It’s not enough just to request an item.