VBA调用WorksheetFunction.SumIfs批量计算速度过慢的优化方案

问题背景 #

使用VBA调用带3个参数的WorksheetFunction.SumIfs对10000行×20列的单元格批量赋值，代码全程运行耗时达2小时。但相同计算逻辑直接在Excel单元格写入公式后拖拽填充，耗时不到10分钟即可完成，且已提前设置xlCalculationManual关闭自动重算，需要提升VBA代码处理效率。

原有代码如下：

Application.Calculation = xlCalculationManual

For Col = 3 To 22
   For Row = 2 To 10000
      FileA.Cells(Row, Col).Value = Application.WorksheetFunction.SumIfs(FileB.Range("A:A"), FileB.Range("D:D"), FileA.Range("A" & Row).Value, FileB.Range("B:B"), FileA.Range("B" & Row).Value, FileB.Range("C:C"), FileA.Cells(1, Col).Value)
   Next
Next

优化方案 #

处理大范围数据时，不要在循环内部调用Application.WorksheetFunction系列函数执行计算，改为先通过Book.Sheet.Range().Formula = "=公式(参数)"的方式给首个单元格写入公式，再调用.Copy方法后使用.PasteSpecial Paste:=xlPasteFormulas批量填充公式，即可大幅提升运行效率。

原写法（耗时2小时） #

' 耗时2小时
For Col = 3 To 22
   For Row = 2 To 10000
      FileA.Cells(Row, Col).Value = Application.WorksheetFunction.SumIfs(FileB.Range("A:A"), FileB.Range("D:D"), FileA.Range("A" & Row).Value, FileB.Range("B:B"), FileA.Range("B" & Row).Value, FileB.Range("C:C"), FileA.Cells(1, Col).Value)
   Next
Next

优化后写法（耗时10分钟） #

' 耗时10分钟
Application.Calculation = xlCalculationManual

FileA.Cells(2, 3).Formula = "=SUMIFS([FileB.XLSX]Sheet1!$A:$A,[FileB.XLSX]Sheet1!$D:$D,$A2,[FileB.XLSX]Sheet1!$B:$B,$B2,[FileB.XLSX]Sheet1!$C:$C,C$1)"
FileA.Cells(2, 3).Copy
FileA.Range(FileA.Cells(2, 3), FileA.Cells(10000, 22)).PasteSpecial Paste:=xlPasteFormulas

Application.Calculation = xlCalculationAutomatic

内容的提问来源于stack exchange，提问作者Munir Sella Bakkar