☰

巨集中的循環迭代 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

更新：

間接命名範圍表由另一個工作表引用，以進一步對資料進行排序和篩選。

每當我在首次開啟檔案後重新計算表格和任何連接的工作表時。 RAM 使用量從 7MB 增加到 20.5 GB。

我猜這是我的工作表中間接公式的大量使用，但無論它是什麼，我們都需要減少 RAM 使用量才能使巨集正常工作。

答案1

要處理 VBA 中的意外錯誤，您可以使用該ON ERROR聲明。

這將導致錯誤被錯誤處理程式碼捕獲，您可以在其中獲取有關錯誤的信息，包括錯誤類型以及遇到錯誤的行號。

在最簡單的錯誤處理中，您可能只是在訊息框中顯示錯誤訊息。這提供了一個起點，有時可能就是解決問題所需的一切。

對於更高級的錯誤處理，您可以檢查值並採取您確定的適當操作之一來解決錯誤並繼續執行。

答案2

這已經解決了我的一些崩潰情況文件>選項>高級>[向下滾動]禁用圖形硬體加速[選中此框]

相關內容