performance - 在 Excel VBA 中迭代类

Question

我已经为名为 Terminal 的实体创建了一个类模块。我有一个方法，通过遍历 175 个单独的工作表并从特定单元格中提取正确的数据来填充此类。这个过程非常快(大约 2 秒)，但是当我尝试将此数据写回新工作表时，需要更长的时间(45 秒)。看起来这个过程应该至少与填充类一样快，因为它永远不必离开工作表，但事实并非如此。下面是我用来将数据写入工作表的过程，我是否忽略了导致其运行如此缓慢的原因？

Application.ScreenUpdating = False
 Dim rowNumber As Integer
 Dim colNumber As Integer
 Dim terminalCode As String
 Dim terminal As clsTerminal

 rowNumber = 7
 colNumber = 1

 For Each terminal In terminals

        'Add hyperlink to each terminal code
        Sheets("Terminal Summary").Hyperlinks.Add Anchor:=Cells(rowNumber, colNumber), Address:="", _
            SubAddress:=terminal.terminalCode + "!A1", TextToDisplay:=terminal.terminalCode

        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 1).Value = "Current"

        'Current period
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 2).Value = terminal.iBShipments
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 3).Value = terminal.oBShipments
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 4).Value = terminal.iBNetRevenue
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 5).Value = terminal.oBNetRevenue
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 6).Value = terminal.iBWeight
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 7).Value = terminal.oBWeight
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 8).Value = terminal.iBMileage
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 9).Value = terminal.oBMileage
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 11).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 12).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 13).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 14).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 15).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 16).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 17).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"

        rowNumber = rowNumber + 1
    Next terminal

编辑我应该注意到终端是终端类的集合

Answer 1

此类代码的一个常见陷阱是，每次将数据写入电子表格时，Excel 都会运行计算(它会评估工作簿中的所有公式，以查看是否需要使用新数据进行计算) .

如果您在循环之前禁用自动计算，然后在循环之后重新启用它，事情会进展得更快:

Application.Calculation = xlCalculationManual
For Each terminal In terminals
...
Next terminal
Application.Calculation = xlCalculationAutomatic