Excel VBA 批量处理台账

VBA 提速的核心是“少操作单元格”,把数据读进数组再写回。

办公效率现场与效率2026-09-16约 1 分钟309 字

几千行的台账,逐格读写会慢到无法接受。VBA 的正确用法是批量读、批量算、批量写。

先关掉刷新

vba
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ... 处理 ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

忘记恢复会导致 Excel 看起来“卡死”,所以用错误处理保证一定恢复。

数组读写

vba
Dim arr As Variant
arr = ws.Range("A2:F" & lastRow).Value   ' 一次读入
' 在内存中处理 arr(i, j)
ws.Range("H2:H" & lastRow).Value = result  ' 一次写回

逐格 Cells(i, j).Value 在几千行时差距是几十倍。

最后一行

vba
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

用 UsedRange 会把格式残留的行也算进去,容易多处理空行。

去重与匹配

用 Scripting.Dictionary 做去重和查找,比 VLOOKUP 循环快得多:

vba
Set dict = CreateObject("Scripting.Dictionary")
If Not dict.Exists(key) Then dict.Add key, value

错误处理

vba
On Error GoTo Cleanup
' ...
Cleanup:
  Application.ScreenUpdating = True
  If Err.Number <> 0 Then MsgBox "出错: " & Err.Description
日期和编号会被“智能”转换

文本日期被识别成日期、长数字变成科学计数、前导零丢失,都是导入导出时的常见问题。输出前把列格式设成文本,或在写入时加单引号。

结构建议

  • 一张表只放一类数据,不要合并单元格;
  • 第一行是字段名,数据从第二行开始;
  • 计算列用公式而不是硬编码值;
  • 宏与数据分开文件存放,便于复用。

小结

数组化、少刷新、明确边界、错误处理。四件事做到,VBA 处理几万行也不慢。

说明:文中的地址、域名、主机名与账号均为脱敏示例;配置片段来自实际环境,但版本与网络条件不同,照搬前请先在测试环境验证。最后校对:2026-09-16。