VBA子程序在第一次执行后速度变慢了很多

Question

我有一个子程序，它生成5个家族中不同投资组合的表现报告。问题是，所讨论的投资组合从来都不一样，每个家庭的投资金额也不一样。因此，我复制粘贴一个模板(这是格式化的和…)，并在报告中的每个投资组合的正确家族中添加格式化的行(包含公式和…)。一切都很好，代码当然不是最优和完美的，但它可以很好地满足我们的需要。问题不在于代码本身，而是当我第一次执行代码时，它运行得非常快(比如1秒)……但是从第二次开始，代码的运行速度明显变慢了(对于与第一次相同的基本任务，代码运行了将近30秒)。我尝试了所有的手动计算，没有刷新屏幕，而且……但这并不是问题的根源。它看起来像一个内存泄漏对我来说，但我找不到问题在哪里!为什么代码运行得很快，但之后却慢了很多?无论报表的长度和文件的内容如何，我都需要关闭excel并重新打开每个报表。

**不确定我是否清楚，但这并不是因为代码使excel文件更大，因为在第一次(快速)执行之后，如果我保存工作簿，关闭并重新打开它，(新的)第一次执行将再次非常快，但如果我做同样的事情而不关闭和重新打开它会非常慢…^!^!

Dim Family As String
Dim FamilyN As String
Dim FamilyP As String
Dim NumberOfFamily As Integer
Dim i As Integer
Dim zone As Integer

Sheets("RapportTemplate").Cells.Copy Destination:=Sheets("Rapport").Cells
Sheets("Rapport").Activate
i = 3
NumberOfFamily = 0
FamilyP = Sheets("RawDataMV").Cells(i, 4)
While (Sheets("RawDataMV").Cells(i, 3) <> "") And (i < 100)
    Family = Sheets("RawDataMV").Cells(i, 4)
    FamilyN = Sheets("RawDataMV").Cells(i + 1, 4)
    If (Sheets("RawDataMV").Cells(i, 3) <> "TOTAL") And _
    (Sheets("RawDataMV").Cells(i, 2) <> "Total") Then
        If (Family <> FamilyP) Then
            NumberOfFamily = NumberOfFamily + 1
        End If
        With Sheets("Rapport")
            .Rows(i + 8 + (NumberOfFamily * 3)).EntireRow.Insert
            .Rows(1).Copy Destination:=Sheets("Rapport").Rows(i + 8 + (NumberOfFamily * 3))
            .Cells(i + 8 + (NumberOfFamily * 3), 6).Value = Sheets("RawDataMV").Cells(i, 2).Value
            .Cells(i + 8 + (NumberOfFamily * 3), 7).Value = Sheets("RawDataMV").Cells(i, 3).Value
        End With
    End If
    i = i + 1
    FamilyP = Family
Wend
For i = 2 To 10
    If Sheets("Controle").Cells(16, i).Value = "" Then
        Sheets("Rapport").Cells(1, i + 11).EntireColumn.Hidden = True
    Else
        Sheets("Rapport").Cells(1, i + 11).EntireColumn.Hidden = False
    End If
Next i
Sheets("Rapport").Cells(1, 1).EntireRow.Hidden = True
'Define printing area
zone = Sheets("Rapport").Cells(4, 3).End(xlDown).Row
Sheets("Rapport").PageSetup.PrintArea = "$D$4:$Y$" & zone

Sheets("Rapport").Calculate
Sheets("RANK").Calculate
Sheets("SommaireGroupeMV").Calculate
Sheets("SommaireGroupeAlpha").Calculate
Application.CutCopyMode = False

结束子

Answer 1

我现在没有笔记本电脑，但你可以尝试一下:

1. 使用option explicit确保在使用所有变量之前声明它们;
2. 我记得原生vba类型的数字不是integer，而是long，整数转换为long，为了节省计算时间使用long代替整数;
3. 您的Family变量被定义为字符串，但您存储的是整个单元格，而不是它们的值，即=cells()而不是=cells().value;
4. 一个经验法则是使用cells(rows.count, 4).end(xlup).row代替cells(3, 4).end(xldown).row.;
5. 条件格式可能会减慢速度;
6. 对于每个循环使用，如果可能的话，而不是while，或者甚至将range复制到变体数组并遍历它(这是最快的解决方案);
7. 使用早期绑定而不是后期绑定，即，以适当的类型尽快定义对象;
8. 不显示打印区域(分页符等);
9. 尝试做一些分析并寻找瓶颈-参见寻找excel vba瓶颈;
10. 只粘贴不需要格式的值;
11. 每次复制/粘贴后清除剪贴板;
12. 在对象使用完毕后设置为Nothing;
13. 使用Value2而不是Value -这将忽略格式，只接受数字值而不是格式化值;

14. 使用表对象并引用它们，例如

    Dim sh_raw As Sheet, sh_rap As Sheetset sh_raw = Sheets("RawDataMV")

，然后用sh_raw代替Sheets("RawDataMV");

Answer 2

我也遇到过同样的问题，但我终于解决了。这听起来很荒谬，但它与打印页面设置有关。显然，Excel在每次更新单元格时都会重新计算它，这就是导致速度减慢的原因。

尝试使用

Sheets("Rapport").DisplayPageBreaks = False

在你的例程开始时，在任何计算之前，

Sheets("Rapport").DisplayPageBreaks = True

在它的末尾。

Answer 3

我也遇到了同样的问题。我远非专业程序员。以上答案对我的程序有帮助，但并没有解决问题。我在一台用了5年的笔记本电脑上运行excel 2013。打开程序而不运行它，转到文件>选项高级，向下滚动到数据，取消勾选"禁用大型数据透视表刷新undo ...."one_answers"禁用大型数据模型操作undo"。你也可以尝试不勾选它们，但降低它们的价值。其中一个或两个似乎正在创建一个不断增加的文件，使宏变慢并最终使其停止。我假设关闭excel会清除他们创建的文件，所以当excel关闭并重新打开至少一段时间时，它运行得很快。有更多知识的人将不得不解释这些变化会带来什么，以及不检查它们会带来什么后果。看来这些更改将应用于您创建的任何新电子表格。如果我有一台更新更强大的电脑，也许这些改动就没有必要了。