我正在看别人的VBA Excel代码。他们在两个循环中都执行ReDim Preserve dataMatrix(7, i)。这是做什么的?

另外,似乎第二个循环只是覆盖了第一个循环中的数据,对吗?

Dim dataMatrix() As String

    Worksheets.Item("ETS").Select
    Do While Trim(Cells(r, 1)) <> ""
       Debug.Print "The line: ", Trim(Cells(r, 1)), r
        r = r + 1
        dataMatrix(1, i) = Trim(Cells(r, 1))    ''file name
        dataMatrix(2, i) = Trim(Cells(r, 2))    ''sample type
        dataMatrix(3, i) = Trim(Cells(r, 3))    ''sample name
        dataMatrix(4, i) = "ETS"    ''
        dataMatrix(5, i) = Trim(Cells(r, 5))    ''Response
        dataMatrix(6, i) = Trim(Cells(r, 6))    ''ISTD Response
        dataMatrix(7, i) = Trim(Cells(r, 10))   ''Calculated Conc
        i = i + 1
        ReDim Preserve dataMatrix(7, i)
    Loop

    r = 5
    Worksheets.Item("ETG").Select
    Do While Trim(Cells(r, 1)) <> ""
       Debug.Print "The line: ", Trim(Cells(r, 1)), r
        r = r + 1
        dataMatrix(1, i) = Trim(Cells(r, 1))    ''file name
        dataMatrix(2, i) = Trim(Cells(r, 2))    ''sample type
        dataMatrix(3, i) = Trim(Cells(r, 3))    ''sample name
        dataMatrix(4, i) = "ETG"
        dataMatrix(5, i) = Trim(Cells(r, 5))    ''Response
        dataMatrix(6, i) = Trim(Cells(r, 6))    ''ISTD Response
        dataMatrix(7, i) = Trim(Cells(r, 10))   ''Calculated Conc
        i = i + 1
        ReDim Preserve dataMatrix(7, i)
    Loop

最佳答案

ReDim修改数据大小时会保留数组中的数据。同样,它也不应该覆盖数据,因为i在循环的每次迭代中都会递增:第一个循环直到遇到空单元格时才会停止,每次迭代将数组长度更改一个,因此,当长度为它将8添加到1,新长度变为i。当第二次迭代发生时,它将新元素写入索引为9的数组的边界,再次修改其长度,以使它成为9元素的长度更长并进行迭代,直到遇到空行。

关于excel - ReDim Preserve做什么?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2916009/

10-10 18:59