☰

如何才能加快 Excel 2003 中的宏运行速度？

tag-icon • tag-icon microsoft-excel macros vba microsoft-excel-2003
如何才能加快 Excel 2003 中的宏运行速度？

我有一个宏，可以将数据从一个单元格复制到另一个单元格并使用VLOOKUP公式等。我的电子表格包含近 2000 行。

当我在 Excel 2003 中运行它时，Excel 开始变慢，因为宏处理了 500 行及以上的行。当它达到第 1000 行时，情况会变得更糟。它需要 5 个多小时才能完成。

然而，在 Excel 2007 中，宏仅运行半小时。

有人能帮我找到一个好的解决办法吗？

答案1

tumchaaditya 建议的博客提供了一些值得实施的极好的建议，但我怀疑它们在这里是否会有所帮助。

对我来说，关键问题是宏变慢了。您是否有如下命令：

StrA = StrA & NewData

ReDim Preserve MyArray(1 To UBound(MyArray)+1)

这些命令使 StrA 和 MyArray 稍微变大一些。对于每个循环，解释器都必须为较大的对象找到空间，从旧对象复制数据，然后释放旧对象以供垃圾回收。每次使 StrA 或 MyArray 稍微变大一些时，这个过程就会花费更长的时间。我不知道为什么 Excel 2003 的问题更严重；也许 Excel 2007 有更好的垃圾回收器。

如果您要累积每一行的数据，那么这样做会更好：

Option Explicit
Type SRowDtl      ' The definition of a User Type must preceed any routines
  Info1 As String
  Info2 As Long
  Info3() As Double
End Type

Sub ProcessRows()

  Dim RowDtl() as SRowDtl
  Dim InxRowDtlCrntMax as Long 

  ReDim RowDtl(NumberOfRows)
  InxRowDtlCrntMax = -1 

  For Each Row ....

     ' Store data from new row
     InxRowDtlCrntMax = InxRowDtlCrntMax+1

     RowDtl(InxRowDtlCrntMax).Info1 = xxx
     RowDtl(InxRowDtlCrntMax).Info2 = yyy
     RowDtl(InxRowDtlCrntMax).Info3(5) = zzz

  Next

如果您不熟悉大多数语言所称的结构和 VBA 所称的用户类型，那么语法可能看起来很奇怪。但是，一旦您熟悉了语法，结构就会让您的代码更加清晰，而且通常速度更快。

答案2

在我查看您的代码之前，我无法诊断出确切的原因。但现在，请参阅以下链接： Office.Com - Excel VBA 性能编码最佳实践
Ozgrid.com VBA 加速 VBA 代码

它们描述了如何优化任何 Excel 宏的性能。

  1. 加速代码并停止屏幕闪烁

    Application.ScreenUpdating=False
Application.ScreenUpdating=True

  2. 执行代码时防止计算

    Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

  3. 如果您有工作表或工作簿事件，则可以加快代码速度。

    还可以停止事件中的无限循环

    Application.EnableEvents = False
Application.EnableEvents = True

  4. 使用对象时使用 With 语句

    With Range("A1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
End With

  5. 当需要将字符串变量恢复为默认的“”时，使用 VbNullString 而不是 =“”

    strWords = "Cats"
strWords = vbNullString

  6. 将相对公式插入单元格区域：比自动填充或复制更快

    Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"

  7. 尽可能避免使用复制和粘贴（绕过剪贴板）

    Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")

  8. 始终正确声明你的变量！

    Dim wSheet as Worksheet
Set wSheet = Sheet1
Set wSheet = Nothing

相关内容