☰

宏中的循环迭代 10-15 次后 Excel 崩溃

tag-icon • tag-icon microsoft-excel worksheet-function vba
宏中的循环迭代 10-15 次后 Excel 崩溃

我有一个动态命名范围，是我几个月前在这里一些人的帮助下创建的。它使用间接方式并且可以工作，但每次我使用鼠标选择一个单元格时，excel 都会重新计算，而且需要很长时间，因为我创建了一些复杂的排序/过滤公式，以便将一些动态列表组织成必要的特定顺序。

我认为这个动态命名范围与排序/过滤器的结合是导致这个缓慢问题的原因。

我怀疑如果我执行循环并重新计算这些公式并通过每个循环命名范围，这些也会导致 Excel 崩溃。

动态命名范围公式为：

=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))

间接公式本质上返回一个特定表格，由于表格的大小（300 行 x 12 列），该表格位于其自己的工作表上。它会随着指定输入单元格的变化而变化。

排序/过滤公式都很类似，只是组织方式略有不同：

=SORTBY(FILTER('Loss Template'!$E:$E, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),FILTER('Loss Template'!$M:$M, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),1,FILTER('Loss Template'!$H:$H, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),1)

这些排序/过滤公式引用了'Loss Template'工作表上的动态命名范围的“打印输出”。我考虑过将列引用更改为动态引用（如果这有帮助的话），这样就不会查看太多行，但我不确定这是否会解决一些缓慢或崩溃的问题，如果不能解决这些问题，我不想提交。

以下是经过 10-15 次迭代后最终崩溃的宏：

Sub CalculateEmods()

    Application.ScreenUpdating = False
    

    Dim filename As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String
    Dim ws As Worksheet
    Dim Calculator As Variant
    Dim xprating As Variant
    Dim emod As Range
    Dim member As Range
    Dim emodsws As Variant
    Dim memberfound As Variant
    Dim i As Integer
    Dim RowCount As Integer
    Dim NeededEmods As Range
    Dim Report As Variant
    

    Set Calculator = ThisWorkbook.Sheets(Array("Loss Template", "Codes", "Rating Data", "Yearly Breakdown", "Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings"))
    Set xprating = ThisWorkbook.Sheets("Experience Rating Sheet")
    Set emod = ThisWorkbook.Sheets("Yearly Breakdown").Range("G334")
    Set member = ThisWorkbook.Sheets("Yearly Breakdown").Range("B2")
    Set emodsws = ThisWorkbook.Sheets("2020Emods")
    Set NeededEmods = emodsws.Range("A2", Range("A2").End(xlDown))
    Set memberfound = NeededEmods.Find(member)
    
    FolderName = "EmodFolder"
    RowCount = NeededEmods.Rows.Count + 1
    Report = Array("Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings")

    For i = 2 To RowCount
        
            Application.EnableEvents = False
            
            member.Value2 = emodsws.Range("A" & i).Value2
            
        'Updates Report for newly entered member
            For Each ws In Calculator
                ws.Calculate
            Next ws
    
            For Each ws In Calculator
                ws.Calculate
                ws.PageSetup.RightFooter = Sheet17.Range("B3").Text & Chr(10) & "Mod Effective 
                     Date:     " & Sheet17.Range("B4")
            Next ws
    
            Application.EnableEvents = True
        
            xprating.Calculate
        
        'Copies emod and pastes it to Emod Worksheet
        emodsws.Cells(i, 4).Value2 = emod.Value2
        
        
        'Prints Emod Report for member as PDF
        filename = ActiveWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & ".pdf"
        Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
        FilePathName = Folderstring & Application.PathSeparator & filename
        
        ThisWorkbook.Sheets(Report).Select
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        FilePathName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False
        
        emodsws.Select
        
    Next i
    
    Application.ScreenUpdating = True
    
    MsgBox "Emod Report Updated!"
    
End Sub

宏会重新计算，因为我一直将工作簿设置为手动（由于重新计算问题）。公式是相互关联的，需要进行几次计算才能确保表格正确更新。

我想指出的另一件事是，尽管工作簿运行缓慢，但它似乎工作正常，但由于它保持打开状态并进行了一些计算，因此一些公式并没有像我使用的计算命令那样重新计算。

像这样的简单公式='Yearly Breakdown'!$B$2无法用ws.Calculate命令来计算。

当我单击此公式所在的单元格并单击输入时，它会按应有的方式进行计算，但如果单击计算工作表，它不会执行任何操作。

底线是，我该如何防止崩溃。我不在乎所需的时间长度，但最关心的是崩溃。

经过检查，它看起来像是内存泄漏

崩溃时的错误代码如下：

Microsoft Error Reporting log version: 2.0

Error Signature:
Exception: EXC_BAD_ACCESS
ExceptionEnumString: 1
Exception Code: KERN_INVALID_ADDRESS (0x0000000000000000)
Date/Time: 2020-07-15 16:24:02 +0000
Application Name: Microsoft Excel
Application Bundle ID: com.microsoft.Excel
Application Signature: XCEL
Application Bitness: x64
Application Version: 16.36.0.20041300
Crashed Module Name: WLMGraphicsDevice
Crashed Module Version: 16.36.0.200413
Crashed Module Offset: 0x00000000000048ac
Blame Module Name: WLMGraphicsDevice
Blame Module Version: 16.36.0.200413
UnsymbolicatedChecksum: 7F136B3BB3D9137C72F75133AE7A2115
Blame Module Offset: 0x00000000000048ac
StackHash: 3458086775a1e3cc-dm_1_main
Application LCID: 1033
Extra app info: Reg=en Loc=0x0409
Build Type: Release
Crashed thread Stack Pointer: 0x_00007ffee1f2ce80
Crashed thread: 0

更新：

另一张工作表引用间接命名范围表来进一步对数据进行排序和过滤。

每次我打开文件后重新计算表格和任何连接的工作表时，内存使用量都会从 7MB 增加到 20.5 GB。

我猜这是我的表格中间接公式的大量使用，但无论它是什么，我们都需要减少宏工作的 Ram 使用量。

答案1

要处理 VBA 中的意外错误，您可以使用该ON ERROR声明。

这将导致错误被您的错误处理代码捕获，您可以在其中获得有关错误的信息，包括错误的类型以及遇到错误的行号。

在最简单的错误处理中，您可能只是在消息框中显示错误信息。这提供了一个起点，有时可能就是您解决问题所需的全部内容。

对于更高级的错误处理，您可以检查值并采取您认为适当的几种操作之一来解决错误并继续执行。

答案2

这为我解决了一些崩溃的情况文件>选项>高级> [向下滚动]禁用图形硬件加速[选中此框]

相关内容