arrays - 错误 9 使用 ReDim Preserve

Question

这个函数返回错误9，为什么？我已经在这里阅读了几个类似的问题，但没有什么能很好地解释我。

我真的很想理解为什么 ReDim Preserve 在这种情况下不起作用，因为从“j+1”开始没有输入(我已经检查了我的单元格)，所以没有覆盖任何内容。

这是代码

Function DadosAnoIndenizações()
ReDim dados(1 To 10000, 1 To 2)
j = 0
For i = 5 To 10000
    If (IsNumeric(Worksheets(2).Cells(i, 8).Value) And Not IsEmpty(Worksheets(2).Cells(i, 8).Value)) Then
        Ano = CInt(Right(Worksheets(2).Cells(i, 8).Value, 4))
        SD = Worksheets(2).Cells(i, 11).Value
        j = j + 1
        dados(j, 1) = Ano
        dados(j, 2) = SD
    End If
Next i
ReDim Preserve dados(1 To j, 1 To 2)
DadosAnoIndenizações = dados
End Function

感谢您的帮助

Answer 1

您正在尝试更改数组的第一个维度。 Redim preserve 只能更改数组的最后一个维度，这就是您收到错误的原因。您可以在 MSDN 的网站上找到此信息:ReDim Statement (Visual Basic) ，特别是“Resizing with Preserve”部分:

• Resizing with Preserve. If you use Preserve, you can resize only the last dimension of the array. For every other dimension, you must specify the bound of the existing array.

  For example, if your array has only one dimension, you can resize that dimension and still preserve all the contents of the array, because you are changing the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension if you use Preserve.

通常，解决方法是让第一个维度成为您的 1 to 2。第二个维度是您的 1 to j然后执行 Application.Transpose将结果写入工作表时。