Excel VBA - redim arr时类型不匹配

I had an dynamic array where is value is from the worksheet. If condition is meet, I would like to delete the element that meet the condition. The code was from this post but it is returning

我有一个动态数组,其中值来自工作表。如果条件满足,我想删除符合条件的元素。代码来自这篇文章,但它正在返回

Type Mismatch

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

在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

1 个解决方案

#1

The Len (or Length) of an array is its Upper Boundary. To decrease the size of the array by 1,

数组的Len(或长度)是其上边界。要将数组的大小减小1,

ReDim Preserve arr(UBound(arr) - 1)

Conversely, the beginning of an array is the LBound or lower boundary. This is usually 0 (zero) or 1 (one) and in a two-dimensioned array you can specify the rank.

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

#1