性能优化

Question

我有一段代码，它占用了运行时的90 %。

大约有8000行，信息存储在A列中。这段代码将这些信息分割到其他列中。运行大约需要15分钟( :O )。对如何提高绩效有什么建议吗？

For i = 2 To Row_Number ' Loop for each row

    If InStr(Cells(i, 1), "//") = 0 Then ' This means that if // appears somewhere in the text we delete all the rows (including this one) (see Else :) and stop the loop
        j = 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop
        LongVIN = Mid(Cells(i, 1), 1, j - 1)
        k = j
        j = j + 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop
        Cells(i, 3) = Mid(Cells(i, 1), k + 1, j - k - 1) ' Model
        k = j
        j = j + 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop
        Cells(i, 4) = Mid(Cells(i, 1), k + 1, j - k - 1) ' Dealer
        k = j
        j = j + 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop

        k = j
        j = j + 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop
        Cells(i, 6) = Mid(Cells(i, 1), k + 1, j - k - 1) ' Region

        k = j
        j = j + 1
        Do Until Mid(Cells(i, 1), j, 1) = ";"
            j = j + 1
        Loop
        Cells(i, 7) = CDate(Mid(Cells(i, 1), k + 1, j - k - 1)) ' Retail Date
        k = j

        Cells(i, 5) = Mid(Cells(i, 1), k + 1, Len(Cells(i, 1)) - k) '(Len - (k+1) +1) Dealer Name

        Cells(i, 1) = Mid(LongVIN, 1, 10)
        Cells(i, 2) = Mid(LongVIN, 11, 7)

    Else:
        Range("A" & i & ":A" & Row_Number).Delete 'ClearContents
        Exit For

    End If

Next i

Answer 1

通过将数据存储在数组中、对数组进行操作并将数据存储回电子表格中，您将看到性能的显著提高。

类似于：

Dim data As Variant
Dim result As Variant

data = Range(Cells(2, 1), Cells(Row_Number, 1))
Redim result (1 To Row_Number, 1 To 7) As Variant

现在，不是从Cells(i, 1)阅读，而是从data(i, 1)阅读，而不是写入Cells(i, n)，而是写入result(i, n)。

在你的代码末尾：

Range(Cells(2, 1), Cells(Row_Number, 7)) = result