Excel Crashes after 10-15 iterations of a loop in a macro

Question

I have a dynamic named range that I created a couple months ago with some help of people on here. It uses indirect and works but every time I just use the mouse to select a cell excel recalculates and it takes a hot minute because I have some complicated sort by/filter formulas that I created in order to organize some dynamic lists into a specific order that is necessary.

This dynamic named range combined with the sort-by/filter's is what I think is causing this slowness issue.

I have suspicions that these are also causing excel to crash if I do loops that recalculate these formulas and named range through each loop.

The dynamic named range formula is:

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

The indirect formula essentially returns a specific table that resides on its own sheet because of the table's size(300 rows by 12 columns). It changes as a specified input cell changes.

The sort-by/filter formulas are all similar with minor differences for organization:

=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)

These sort-by/filter formulas reference a "printout" of the dynamic named range that is on the 'Loss Template' Sheet. I was thinking of possibly changing the column references to dynamic references if that helps so not so many rows are being looked at but I am not sure if that will fix some of the slowness or crashing issue and did not want to commit if it wasn't going to fix it.

Here is the Macro that eventually crashes after 10-15 iterations:

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

The Macro recalculates things because I have the workbook set to Manual all the time (due to the recalculation problem). The formulas are interconnected and need a couple of calculations to make sure that the tables get updated correctly.

Another thing I would like to note is that the workbook seems to work fine despite the slowness but as it remains open and does a few calculations for a while some of the formulas are not recalculating like they should per the calculate command I use.

A simple formula like ='Yearly Breakdown'!$B$2 doesn't calculate with ws.Calculate command.

When I click the cell this formula is in and click enter it calculates like it should but if click the calculate worksheet it doesn't do anything.

Bottom Line how can i prevent the crashing. I do not mind the length of time required but mostly the crashing.

After reviewing it looks like a memory leak

Here is Error Code when it crashes:

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

UPDATE:

The indirect named range table is referenced by another sheet to sort and filter data even further.

Whenever I recalculate the table and any connected sheets after intially opening the file. The Ram Usage goes from 7MB to 20.5 GB.

Im guessing it is the big use of indirect formulas in my sheet but whatever it is we need to reduce the Ram usage for the macro to work.

Answer 1

To handle unexpected errors in VBA, you may use the ON ERROR statement.

This will cause the error to be caught by your error-handling code, where you may obtain information about the error, including the type of error, and the line number where the error was encountered.

In the simplest error-handling, you may just display the error information in a messagebox. This gives a starting-point and may sometimes be all you need to resolve the problem.

For more advance error-handling, you can check values and take one of several actions you have determined are appropriate to resolve the error and continue execution.

Answer 2

this has solved a few crashing scenarios for me file > options > advanced> [scroll down] disable graphics hardware acceleration [check this box]