通过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 

Answer 2

您已经有了很大的储蓄（关闭自动计算，同时写），但有是未来的一些其他小技巧。

首先，每次从VBA写入单元时，都会由于VBA进入工作簿/表单/单元地址并执行写入而导致开销。在一次通话中写入很多单元只会导致一次开销。因此，将多个值包装到一个数组中，并将该数组写入多个单元格会增加时间。几条线不值得，但是值得数百的努力。

此外，每个“点”还有一点点开销。 “点”表达式中的术语（如Sheets("Terminal Summary").Cells(rowNumber, colNumber + 2)）需要Excel/VBA来确定每个调用中涉及哪些对象。在某些情况下（特别是在引用远程对象时）开销可能很大。 VB为我们提供了With...End With构造，以减少需要继续解析这些引用：以点开头的每个表达式自动引用下一个最外面的With中的对象。

所以我们可以得到这样的：

With Sheets("Terminal Summary") 
    .Cells(rowNumber, colNumber + 2).Resize(1, 8) = terminal.InfoArray 
    .Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)" 
    .Cells(rowNumber, colNumber + 11).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)" 
    .Cells(rowNumber, colNumber + 12).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)" 
    .Cells(rowNumber, colNumber + 13).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)" 
    .Cells(rowNumber, colNumber + 14).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8]/100),0)" 
    .Cells(rowNumber, colNumber + 15).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8]/100),0)" 
    .Cells(rowNumber, colNumber + 16).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)" 
    .Cells(rowNumber, colNumber + 17).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)" 
End With 

我掖阵列东西进入终端类，像这样：

Public Property Get InfoArray() As Variant 
    InfoArray = Array(iBShipments, oBShipments, iBNetRevenue, oBNetRevenue, iBWeight, oBWeight, iBMileage, oBMileage) 
End Property 

的公式可能会输出更高效地终端信息全部完成后，每列写入一次。

有些事情要考虑，至少...

Answer 3

你已经有90％的答案，我会给的，但这里有一个进一步的性能提示。而不是做：

Sheets("Terminal Summary").Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)" 

你可以使用的事实，你在一个语句中给相同的公式列中的所有单元格，并做到这一点：

Sheets("Terminal Summary").Cells(7, 11).Resize(terminals.Count, 1).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"