☰

VBA - for循环和二维数组的类型不匹配错误

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2016
VBA - for循环和二维数组的类型不匹配错误

我想要做的是将一个数组复制到一个临时数组，同时调整原始数组的大小，然后将临时数组复制回调整大小后的原始数组。

但是当第一个 for 循环运行时，我遇到了错误“类型不匹配”。

我做错了什么导致收到此错误？

Public Sub UpdateNamesPaid(NameValue As String, Paid As String)

    NamesPaid(NamesPaidSize, 0) = NameValue
    NamesPaid(NamesPaidSize, 1) = Paid

    NamesPaidSize = NamesPaidSize + 1
    Dim TempArray() As Variant
    ReDim TempArray(0 To NamesPaidSize, 0 To 1)

    Dim i As Integer
    Dim j As Integer

    i = 0
    j = 0

    For i = 0 To UBound(NamesPaid(i, j)) 

        For j = 0 To UBound(NamesPaid(i, j))

            TempArray(i, j) = NamesPaid(i, j)

        Next j
    Next i

    ReDim NamesPaid(0 To NamesPaidSize, 0 To 1)
    NamesPaid() = TempArray()

End Sub

答案1

您误解了UBound()函数参数的含义：第一个参数是数组名称，第二个参数是您正在查看的维度。
正确的代码应该是：

    Public Sub UpdateNamesPaid(NameValue As String, Paid As String)
    Dim i As Long, j As Long
    Dim NamesPaidSize As Long
    Dim NamesPaid() As String
    ReDim NamesPaid(0 To 0, 0 To 1)

    NamesPaidSize = LBound(NamesPaid, 1) ' or whatever you meant...
    NamesPaid(NamesPaidSize, 0) = NameValue
    NamesPaid(NamesPaidSize, 1) = Paid

    Dim TempArray() As String
    ReDim TempArray(0 To NamesPaidSize, 0 To 1)


    For i = LBound(NamesPaid, 1) To UBound(NamesPaid, 1)
        For j = LBound(NamesPaid, 2) To UBound(NamesPaid, 2)
            TempArray(i, j) = NamesPaid(i, j)
        Next j
    Next i

    NamesPaidSize = NamesPaidSize + 1
    ReDim NamesPaid(0 To NamesPaidSize, 0 To 1)

   ' NamesPaid = TempArray
    For i = LBound(TempArray, 1) To UBound(TempArray, 1)
        For j = LBound(TempArray, 2) To UBound(TempArray, 2)
             NamesPaid(i, j) = TempArray(i, j)
        Next j
    Next i
End Sub

请注意，对于 1 的增量，这可能非常低效。您可以将初始数组的大小设为 10，填充它，然后放入复制步骤并再增加 10。

相关内容