优化Excel VBA变更事件宏，提升大数据量下的运行效率

针对批量输入1000条物料数据时的性能问题，可从以下几个核心方向优化：

• 禁用事件与屏幕刷新
  批量输入时，Worksheet_Change会频繁触发，且屏幕实时刷新会大幅拖慢速度。在宏开头添加以下代码，处理完成后再恢复：

  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual ' 临时关闭自动计算
  

  处理结束后务必恢复：

  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  

• 精准限定触发范围
  不要让宏响应整个B列的变更，只监控B15及以下的有效数据区域。通过Intersect判断变更目标是否在范围内，不在则直接退出：

  Dim targetRange As Range
  Set targetRange = Me.Range("B15:B" & Me.Cells(Me.Rows.Count, "B").End(xlUp).Row)
  If Intersect(Target, targetRange) Is Nothing Then
      ' 恢复设置后退出
      Application.EnableEvents = True
      Exit Sub
  End If
  

• 批量操作替代逐单元格处理
  避免循环逐个单元格写入公式或格式，直接对整行/整列区域批量赋值。比如填充A列序号：

  Dim lastRow As Long
  lastRow = Me.Cells(Me.Rows.Count, "B").End(xlUp).Row
  Me.Range("A15:A" & lastRow).Formula = "=ROW()-14" ' 批量写入公式
  

  格式设置也统一执行：

  Me.Range("E15:E" & lastRow).NumberFormat = "#,##0" ' 批量设置数量列格式
  

• 减少跨工作表查询的重复开销
  原宏中如果用公式（如VLOOKUP）跨Stocklist5104/5102、PriceList查询数据，每次单元格计算都会触发跨表读取。建议把辅助表数据加载到内存数组，在VBA中完成匹配后批量写入结果：

  ' 读取Stocklist5104数据到数组
  Dim stockArr As Variant
  stockArr = ThisWorkbook.Worksheets("Stocklist5104").UsedRange.Value
  ' 循环匹配后批量写入目标列
  Me.Range("C15:C" & lastRow).Value = 匹配后的数组结果
  

• 延迟汇总行更新
  把汇总行的计算放在所有数据处理、格式设置完成后，仅执行一次，避免中间反复更新：

  Me.Range("T" & lastRow + 1).Formula = "=SUM(T15:T" & lastRow & ")"
  

• 添加错误处理保障
  确保即使宏执行出错，也能恢复Excel的事件、计算和屏幕刷新功能，避免后续操作异常：

  On Error GoTo Cleanup
  ' 宏核心代码...
  
  Cleanup:
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  If Err.Number <> 0 Then MsgBox "执行出错：" & Err.Description
  

内容的提问来源于stack exchange，提问作者San Jay