英文:
VBA subscript out of range for variant array after sorting
问题
在下面的函数中，我从Excel文件中读取了一个向量变量中的11个值。然后我对它们进行排序并尝试从排序后的数组中获取一个条目。然而，我得到了一个下标超出范围的错误，尽管下标小于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) '这里是11
get_value_from_sorted_array = sorted_vector(5) '这里会导致下标超出范围错误
End Function
我尝试更改变量的类型等等。看起来应该有一个简单的解决方法，但我找不到它。
解决方案: sorted_vector(5, 1)。
英文:
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).
答案1
得分: 0
当将一组单元格传递给 get_value_from_sorted_array() 时，变量 vector 被分配为一个基于1的、二维数组。即使传递的范围是单列或单行内的一组单元格，情况也是如此。
所以，假设 CountA 返回 10。这意味着 sorted_vector 将被声明为一个 10 行 1 列 的数组。因此，您需要指定第二维度的索引号，如下所示...
get_value_from_sorted_array = sorted_vector(5, 1)
有趣的是，如果 CountA 的结果首先分配给一个单独的变量，然后将该变量用于 ReDim sorted_vector，那么它将被声明为一个基于0的、一维数组。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论