从Excel表中提取数据到VBA:空变量/数组，但UBound返回数字

Question

我试图从Excel表中提取文本数据到数组(在这种情况下定义为变体)。

下面的代码没有返回期望的结果:当我尝试访问SearchItems变量数组中的元素时，弹出一个错误，说下标超出范围。

然而，当我运行UBound(SearchItems)时，系统返回LR的值(而不是LR-1?)。

在任何情况下，这是否表明数据已经加载到数组中?

Sub Testing()
Dim SearchItems As Variant
Dim LR As Integer
LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A
SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value
End Sub

Answer 1

您正在处理一个二维数组:

Sub Testing()
    Dim SearchItems As Variant
    Dim LR As Integer, i As Integer
    LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A
    SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value
    For i = 1 To LR
        MsgBox SearchItems(i, 1)
    Next i
End Sub

Answer 2

数组的搜索项从0开始，所以ubound当然会在你认为它的大小上加1。

如果您需要Ubound工作(如帖子标题所示):

Sub Testing()
Dim SearchItems() As Variant 'we want SeachItems to be a dynamic array
Dim LR As Long, i As Long
with Sheets("MySheet")
    LR = .Cells(.Rows.Count, 1).End(xlUp).Row 'an other way of Getting the number of cells in column A, note the '.' before rows
    redim SearchItems ( 1 to LR, 1 to 1) ' this way ubound should work
    SearchItems = .Range(.cells(1,1) , .cells(LR,1) ).Value 'an other way of doing it (strangely faster considering its bigger code, tested it)
end with
For i = 1 To LR 'or to Ubound (SearchItems)
    'do stuff with  SearchItems(i, 1) 
Next i

'to write it back to the worksheet :
Sheets("MySheet").Range("A1:A" & LR).Value = SearchItems
End Sub