CODEWITHSUNDEEP
excelvba
nrb
nrb

Reputation: 29

"Subscript out of range" on array converted from Range

I saw quite a number of questions/answers along these lines, but after reading a bunch of them, I'm still confused. I'm sorry if this is the nᵗʰ time a variant of this has been asked.

I can't figure out why this code dies on line 5 with a "subscript out of range" error in VBA (Excel for Mac v16.38):

Public Function array_test()
    Dim arr As Variant
    Dim array_slice As Variant
    arr = Range("TestData!B27:F32").Value2
    array_slice = arr(2)  'dies here with error 9
    array_test = array_slice
End Function

Looking at the values pane, arr is clearly a Variant/Variant(1 to 6, 1 to 5) with all the expected data. There is nothing special about the cells, just non-formula data.

Even if I change the declarations to arr() and array_slice() or remove ".Value2", I get the same results. Even trying Application.WorksheetFunction.Index(arr, 2) rather than arr(2) gets me nowhere.

What am I missing?

P.S. I'm a C/Python programmer normally, so I'm thinking of arrays in those terms.

Upvotes: 0

Views: 622

Answers (2)

JvdV
JvdV

Reputation: 75840

Usually one would loop an 2D-array for it's elements, however, since you specifically mentioned you would like to slice it through VBA, you could use Application.Index. For example try:

array_slice = Application.Index(arr, 2, 0) 'Slice 2nd row into 1D-array.

The idea here is to feed Application.Index with a static '2' which represents the row of interest. In the same fashion you could slice a specific column of interest, though if you need this to be an 1D-array, you'd need to use Application.Transpose, however there are limitations to this method:

With Application
    array_slice = .Transpose(.Index(arr, 0, 2)) 'Slice 2nd column into 1D-array.
End With

Upvotes: 1

FunThomas
FunThomas

Reputation: 29171

When you copy data from a Range and the Range contains more than one cell, you get n 2-dimensional array in any case (even if you have only one row or one column).

To access a single value from that array, you have to provide both indices, like arr(2, 1). However, if you want to get a one-dimensional array, containing all values from a row (first index) or a column (second index), you need to create that array by your own - there is no slice function in VBA. You can dimension an array at runtime using the ReDim-command:

Dim array_slice(), i As Long
ReDim array_slice(LBound(arr, 1) To UBound(arr, 1))
For i = LBound(arr, 1) To UBound(arr, 1)
    array_slice(i) = arr(i, 2)
Next i

To get the values of a column, use

Dim array_slice(), i As Long
ReDim array_slice(LBound(arr, 2) To UBound(arr, 2))
For i = LBound(arr, 2) To UBound(arr, 2)
    array_slice(i) = arr(2, i)
Next i

Upvotes: 0

Related Questions