arrays - Excel VBA - redim arr 时类型不匹配

Question

我有一个动态数组，其中值来自工作表。
如果满足条件，我想删除满足条件的元素。代码来自 this post但它正在回归

Type Mismatch

在 ReDim Preserve arr(Len(arr) - 1)

Sub arrtest()
Dim arr As Variant
Dim i As Integer

ReDim arr(1 To 1)

Dim cnt As Long
cnt = 0

For i = 1 To Cells(Rows.Count, "L").End(xlUp).Row

    If Cells(i, "A").Value = "-2" Then
        cnt = cnt + 1
        ReDim Preserve arr(1 To cnt)
        arr(cnt) = Cells(i, "A").Value
    End If

Next i

    For i = 1 To cnt
        Debug.Print "This is arr: "; arr(i)

     If arr(i) = "TEST" Then
      Call DeleteElementAt(i, arr)
      Debug.Print "This is new arr: "; arr(i)
      Else
      Debug.Print "Nothing is deleted"
      End If
    Next

End Sub

Public Sub DeleteElementAt(ByVal index As Integer, ByRef arr As Variant)
       Dim i As Integer

        ' Move all element back one position
        For i = index + 1 To UBound(arr)
            arr(i - 1) = arr(i)
        Next
        ' Shrink the array by one, removing the last one
        ReDim Preserve arr(Len(arr) - 1)
End Sub

Answer 1

数组的长度(或长度)是它的上边界。要将数组的大小减小 1，

ReDim Preserve arr(UBound(arr) - 1)

相反，数组的开头是 LBound 或下边界。这通常是 0(零)或 1(一)，在二维数组中，您可以指定等级。