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

VBA subroutine slows down a lot after first execution

提问by user3666381

I have a subroutine that generates a report of performance of different portfolios within 5 families. The thing is that the portfolios in question are never the same and the amount in each family neither. So, I copy paste a template (that is formated and...) and add the formated row (containing the formula and...) in the right family for each portfolio in the report. Everything works just fine, the code is not optimal and perfect of course, but it works fine for what we need. The problem is not the code itself, it is that when I execute the code the first time, it goes really fast (like 1 second)... but from the second time, the code slows down dramatically (almost 30 second for a basic task identical to the first one). I tried all the manual calculation, not refreshing the screen and ... but it is really not where the problem comes from. It looks like a memory leak to me, but I cannot find where is the problem! Why would the code runs very fast but sooooo much slower right after... Whatever the length of the report and the content of the file, I would need to close excel and reopen it for each report.

我有一个子程序，可以生成 5 个系列中不同投资组合的表现报告。问题是所讨论的投资组合永远不会相同，每个家庭的金额也不相同。因此，我复制粘贴一个模板（即格式化和...），并在报告中每个投资组合的正确系列中添加格式化行（包含公式和...）。一切都很好，当然代码不是最佳和完美的，但它可以很好地满足我们的需要。问题不在于代码本身，而是当我第一次执行代码时，它运行得非常快（比如 1 秒）......任务与第一个相同）。我尝试了所有的手动计算，没有刷新屏幕和......但它真的不是问题出在哪里。对我来说看起来像是内存泄漏，但我找不到问题出在哪里！为什么代码会运行得非常快，但之后就慢了很多……无论报告的长度和文件的内容如何，​​我都需要关闭 excel 并为每个报告重新打开它。

**Not sure if I am clear, but it is not because the code makes the excel file larger or something, because after the first (fast) execution, if I save the workbook, close and reopen it, the (new) first execution will again be very fast, but if I would have done the same excat thing without closing and reopening it would have been very slow...^!^!

**不确定我是否清楚，但这不是因为代码使excel文件变大或什么，因为在第一次（快速）执行之后，如果我保存工作簿，关闭并重新打开它，（新的）第一次执行将再次非常快，但如果我在不关闭和重新打开的情况下做同样的 excat 事情，它会非常慢......^！^！

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:$Y$" & zone


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

End Sub

结束子

回答by MP?kalski

I do not have laptop with me at the moment but you may try several things:

我目前没有笔记本电脑，但您可以尝试以下几件事：

1. use option explicitto make sure you declare all variables before using them;
2. from what I remember native vba type for numbers is not integerbut long, and integers are converted to long, to save the computation time use long instead of integers;
3. your Family variables are defined as strings but you store in them whole cells and not their values i.e. =cells()instead of =cells().value;
4. a rule of a thumb is to use cells(rows.count, 4).end(xlup).rowinstead of cells(3, 4).end(xldown).row.;
5. conditional formatting may slow down things a lot;
6. use for eachloop on a range if possible instead of while, or even copy range to variant array and iterate over that (that is the fastest solution);
7. use early binding rahter of late binding, i.e., define objects in a proper type as soon a possible;
8. do not show printing area (page breaks etc.);
9. try to do some pofiling and look for the bottlenecks - see finding excel vba bottlenecks;
10. paste only values if you do not need formats;
11. clear clipboard after each copy/paste;
12. set objects to Nothingafter finishing using them;
13. use Value2instead of Value- that will ignore formatting and take only numeric value instead of formatted value;

14. use sheet objects and refer to them, for example

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

1. 使用选项显式以确保在使用它们之前声明所有变量；
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. 如果可能，请在范围上使用for 每个循环而不是while，或者甚至将范围复制到变体数组并对其进行迭代（这是最快的解决方案）；
7. 使用早绑定而不是晚绑定，即尽快以适当的类型定义对象；
8. 不显示打印区域（分页符等）；
9. 尝试进行一些分析并寻找瓶颈 - 请参阅寻找 excel vba 瓶颈；
10. 如果您不需要格式，则仅粘贴值；
11. 每次复制/粘贴后清除剪贴板；
12. 使用完对象后将其设置为Nothing；
13. 使用Value2而不是Value- 这将忽略格式并仅采用数值而不是格式化值；

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

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

and then use sh_rawinstead of Sheets("RawDataMV")everywhere;

然后使用sh_raw而不是Sheets("RawDataMV")无处不在；

回答by szaleski

I had the same problem, but I finally figured it out. This is going to sound ridiculous, but it has everything to do with print page setup. Apparently Excel recalculates it every time you update a cell and this is what's causing the slowdown.

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

Try using

尝试使用

Sheets("Rapport").DisplayPageBreaks = False

at the beginning of your routine, before any calculations and

在您的例行程序开始时，在任何计算和

Sheets("Rapport").DisplayPageBreaks = True

at the end of it.

在它的最后。

回答by Aero

I had the same problem. I am far from expert programer. The above answers helped my program but did not solve the problem. I'm running excel 2013 on a 5 year old lap top. Open the program without running it, go to File>OptionsAdvanced, Scroll down to Data and uncheck "Disable undo for large Pivot table refresh...." and "Disable undo for large data Model operation". You could also try leaving them checked but decreasing their value. One or both of these seem to be creating a ever increase file that slows the macro and eventual grinds it to a stop. I assume closing excel clears the files they create so that's why it runs fast when excel is closed and reopened at least for a while. Someone with more knowledge will have to explain what these changes will do and what the consequences are of unchecking them. It appears these changes will be applied to any new spread sheets you create. Maybe these changes would not be necessary if I had a newer more powerful computer.

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