第一次执行后，VBA子例程的速度大大降低 [英] VBA subroutine slows down a lot after first execution

问题描述

我有一个子例程，可以生成5个系列中不同投资组合的业绩报告.问题是，所讨论的投资组合永远不会相同，每个家庭的投资额也不会相同.因此，我复制粘贴模板(已格式化并...)，然后在报表的每个投资组合的正确族中添加已格式化的行(包含公式和...).一切工作都很好，代码当然不是最优的，但是可以满足我们的需求.问题不在于代码本身，而是当我第一次执行代码时，它的运行速度非常快(例如1秒)...但是从第二次开始，代码就显着放慢了速度(基本时间大约为30秒)任务与第一个任务相同).我尝试了所有手动计算，没有刷新屏幕，但是...确实不是问题出处.对我来说，这似乎是内存泄漏，但是我找不到问题所在！为什么代码运行得很快，但是之后却变得慢得多...无论报告的长度和文件的内容如何，​​我都需要关闭excel并为每个报告重新打开它.

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.

**不确定我是否清楚，但这不是因为代码使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...^!^!

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

结束子

推荐答案

我暂时没有笔记本电脑，但您可以尝试以下几种方法:

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

1. 使用显式选项来确保在使用它们之前声明所有变量；
从我记得的数字来看，
2. 不是原生的vba类型，而是 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 ，甚至将范围复制到变量数组并对其进行迭代(这是最快的解决方案)；
7. 使用后期绑定的早期绑定功能，即尽快定义适当类型的对象；
8. 不显示打印区域(分页符等)；
9. 尝试进行配置并查找瓶颈-请参见

1. use option explicit to make sure you declare all variables before using them;
2. from what I remember native vba type for numbers is not integer but 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).row instead of cells(3, 4).end(xldown).row.;
5. conditional formatting may slow down things a lot;
6. use for each loop 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 Nothing after finishing using them;
13. use Value2 instead 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

将sh_raw设为工作表，sh_rap设为工作表 设置sh_raw = Sheets("RawDataMV") set sh_rap = Sheets("Rapport")

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

，然后在各处使用sh_raw而不是Sheets("RawDataMV")

and then use sh_raw instead of Sheets("RawDataMV") everywhere;

这篇关于第一次执行后，VBA子例程的速度大大降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！