VBA subscript out of range for variant array after sorting

Question

in the following function, I read in for example 11 values in the vector variable from an excel file. Then I sort them and try to get an entry from the sorted array. However, I get a subscript out of range error, even though the subscript is smaller than UBound(sorted_vector)

Function get_value_from_sorted_array(vector As Variant) As Variant
    ReDim sorted_vector(WorksheetFunction.CountA(vector)) As Variant
    sorted_vector = WorksheetFunction.sort(vector)
    
    Dim sorted_vector_ubound As Double
    sorted_vector_ubound = UBound(sorted_vector) 'this is 11
    
    get_value_from_sorted_array = sorted_vector(5) 'this gives subscript out of range
    
End Function

I tried changing the types of the variables and so on. It seems like there should be an easy fix; however, I can't find it.

Solution: sorted_vector(5, 1).

Answer 1

When passing a range of cells to get_value_from_sorted_array(), the variable vector gets assigned a 1-based, 2-dimensional array. And this is the case even when the range being passed is a range of cells within a single column or row.

So, let's say the CountA returns 10. This means that sorted_vector will be declared as a 10-Row by 1-Column array. Therefore, you'll need to specify the index number for the second dimension, as follows...

get_value_from_sorted_array = sorted_vector(5,1)

Interestingly, though, if the result from CountA is first asigned to a separate variable, and then the variable is used to ReDim sorted_vector, it instead gets declared as a 0-based, 1-dimensional array.