Loop keeps on crashing without an error code

0

I have a loop that copies a value from a source range and inserts it into a calculator and that triggers a worksheet change event that updates the calculator and a report. The code then copies and pastes the results into a results column. After the result is recorded, it saves the updated report as pdf then goes on to the next item on the source range. After exactly 18 iterations Excel completely crashes and exits the program.

Here is the main macro code:

Sub CalculateEmods()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim emod As Range
    Dim member As Range
    Dim emodsws As Variant
    Dim i As Integer
    Dim RowCount As Integer
    Dim NeededEmods As Range
    
    Dim FolderName As String
    Dim filename As String
    Dim Folderstring As String
    Dim FilePathName As String
    Dim Report As Variant
    
    Set emodsws = ThisWorkbook.Sheets("2020Emods")
    Set NeededEmods = emodsws.Range("A2", Range("A2").End(xlDown))
    
    FolderName = "EmodFolder"
    RowCount = NeededEmods.Rows.Count - 1

    
    For i = 2 To RowCount
    
        
        Set emod = ThisWorkbook.Sheets("Yearly Breakdown").Range("G334")
        Set member = ThisWorkbook.Sheets("Yearly Breakdown").Range("B2")

        Report = Array("Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings")
    
        'Changes member_ID on "Yearly Breaksown" sheet
        Application.EnableEvents = True
            member.Value2 = emodsws.Range("A" & i).Value2
        Application.EnableEvents = False
        
        'Copies emod and pastes it to Emod Worksheet
        emodsws.Cells(i, 4).Value2 = emod.Value2
        
        Set emod = Nothing
        Set member = Nothing
        
        'Prints Emod Report for member as PDF from function
        filename = ThisWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & "_" & ThisWorkbook.Sheets("Yearly Breakdown").Range("F2") & ".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
    Application.EnableEvents = True
    
    MsgBox "Emod Reports Created!"
    
    
End Sub

Here is the Worksheet Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)

    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Intersect(Target, Range("B2")) Then
    
        Dim primaryarray As Range
        Dim secondaryarray As Range
        Dim rw As Range
        
        Set primaryarray = ThisWorkbook.Sheets("Experience Rating Sheet").Range("B9:M322")
        Set secondaryarray = ThisWorkbook.Sheets("Mod Snapshot").Range("A29:E39")
        
        ' unhide all rows before we begin
        primaryarray.EntireRow.Hidden = False
        secondaryarray.EntireRow.Hidden = False
        
        'function recalculates sheets that wil change number of rows to hide
        Call RecalculateSheets
        
        'hides rows based on criteria set in function
        For Each rw In primaryarray.Rows
            rw.EntireRow.Hidden = BlankOrZero(rw.Cells(3)) And BlankOrZero(rw.Cells(8))
        Next rw
        
        For Each rw In secondaryarray.Rows
            rw.EntireRow.Hidden = BlankOrZero(rw.Cells(1))
        Next
    
        Set primaryarray = Nothing
        Set secondaryarray = Nothing
        
    End If
        
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub


Function BlankOrZero(c As Range)
    BlankOrZero = Len(c.Value) = 0 Or c.Value = 0
End Function

Function RecalculateSheets()

    Dim ws As Worksheet
    Dim Report As Variant
    Dim Calculator As Variant
    
    Set Report = ThisWorkbook.Sheets(Array("Cover Sheet", "Ag Loss Sensitivity", _
                                                        "Experience Rating Sheet", "Loss Ratio Analysis", _
                                                        "Mod Analysis&Strategy Proposal", "Mod Snapshot", _
                                                        "Mod & Potential Savings"))
                                                        
    Set Calculator = ThisWorkbook.Sheets(Array("Loss Template", "Codes", "Yearly Breakdown"))
    
    Me.Range("B4").Calculate
    
    For Each ws In Calculator
        ws.Calculate
    Next ws
    
    For Each ws In Report
        ws.Calculate
        ws.PageSetup.RightFooter = Sheet17.Range("B3").Text & Chr(10) & "Mod Effective Date:     " & Sheet17.Range("B4")
    Next ws
    
    Set Calculator = Nothing
    Set Report = Nothing
    
End Function

At first I thought it was a flaw in how I wrote the VBA code and initially it would try to use 20 GB of Ram because of the calculations that were happening on a specific sheet. After researching the ram usage problem, I optimized the workbook more got it down to 2GB of Ram usage.

Even though the Ram usage was reduced this dramatically, after 18 iterations it STILL is crashing causing me to think it actually is how I wrote the VBA code.

Here is the last group of lines and the last thread before the crash from the error reporting window that pops up.

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-22 15:43:21 +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: 0FA8E2FBB61BC7D1B78545BD91626209
Blame Module Offset: 0x00000000000048ac
StackHash: 3022bf6927992f79-dm_1_main
Application LCID: 1033
Extra app info: Reg=en Loc=0x0409
Build Type: Release
Crashed thread Stack Pointer: 0x_00007ffee9cd4340
Crashed thread: 0


Thread 0 crashed (0x6a0897):

#  1  0x000000010ee6d8ac in  (WLMGraphicsDevice + 0x00000000000048ac) 0x0000000000000000
#  2  0x0000000107f39556 in  (Microsoft Excel + 0x0000000002033556) 0x0000000000000000
#  3  0x0000000107f1e269 in  (Microsoft Excel + 0x0000000002018269) 0x00007ffee9cd4390
#  4  0x0000000107f1df94 in  (Microsoft Excel + 0x0000000002017f94) 0x00007ffee9cd4780
#  5  0x0000000107f1f052 in  (Microsoft Excel + 0x0000000002019052) 0x00007ffee9cd4800
#  6  0x00000001084aa7bb in  (Microsoft Excel + 0x00000000025a47bb) 0x00007ffee9cd48c0
#  7  0x00000001084aae6d in  (Microsoft Excel + 0x00000000025a4e6d) 0x00007ffee9cd4a20
#  8  0x00000001084ace0a in  (Microsoft Excel + 0x00000000025a6e0a) 0x00007ffee9cd4ba0
#  9  0x00000001084acb5c in  (Microsoft Excel + 0x00000000025a6b5c) 0x00007ffee9cd4bd0
# 10  0x000000010841fbb2 in  (Microsoft Excel + 0x0000000002519bb2) 0x00007ffee9cd4ed0
# 11  0x000000010841ae7b in  (Microsoft Excel + 0x0000000002514e7b) 0x00007ffee9cd5160
# 12  0x000000010808a523 in  (Microsoft Excel + 0x0000000002184523) 0x00007ffee9cd5250
# 13  0x0000000108084f28 in  (Microsoft Excel + 0x000000000217ef28) 0x00007ffee9cd5290
# 14  0x000000010808477d in  (Microsoft Excel + 0x000000000217e77d) 0x00007ffee9cd5520
# 15  0x0000000107f76c7a in  (Microsoft Excel + 0x0000000002070c7a) 0x00007ffee9cd5740
# 16  0x000000010832fddd in  (Microsoft Excel + 0x0000000002429ddd) 0x00007ffee9cd5c90
# 17  0x0000000108478cb1 in  (Microsoft Excel + 0x0000000002572cb1) 0x00007ffee9cd5fa0
# 18  0x0000000108158bce in  (Microsoft Excel + 0x0000000002252bce) 0x00007ffee9cd62c0
# 19  0x0000000107cb38b7 in  (Microsoft Excel + 0x0000000001dad8b7) 0x00007ffee9cd6480
# 20  0x0000000108a9dee1 in  (Microsoft Excel + 0x0000000002b97ee1) 0x00007ffee9cd7cc0
# 21  0x0000000108358b47 in  (Microsoft Excel + 0x0000000002452b47) 0x00007ffee9cd7dd0
# 22  0x000000010835c7a5 in  (Microsoft Excel + 0x00000000024567a5) 0x00007ffee9cd81e0
# 23  0x0000000108aa11e1 in  (Microsoft Excel + 0x0000000002b9b1e1) 0x00007ffee9cd83e0
# 24  0x0000000108a9d6a7 in  (Microsoft Excel + 0x0000000002b976a7) 0x00007ffee9cd84e0
# 25  0x00000001088c7ae1 in  (Microsoft Excel + 0x00000000029c1ae1) 0x00007ffee9cd8540
# 26  0x0000000108aa0aaf in  (Microsoft Excel + 0x0000000002b9aaaf) 0x00007ffee9cdaa40
# 27  0x00000001060c7c98 in  (Microsoft Excel + 0x00000000001c1c98) 0x00007ffee9cdae00
# 28  0x00000001a2e8f125 in  ( + 0x0000000000000000) 0x00007ffee9cdae50
# 29  0x000000011399c3c3 in  (Visual Basic for Applications + 0x000000000016c3c3) 0x00007ffee9cdaea0
# 30  0x00000001139943eb in  (Visual Basic for Applications + 0x00000000001643eb) 0x00007ffee9cdb000
# 31  0x00000001acc97282 in  ( + 0x0000000000000000) 0x00007ffee9cdb140
# 32  0x0000000111344398 in  (OLEAutomation + 0x0000000000077398) 0x00007ffee9cdb170
# 33  0x000000011134420b in  (OLEAutomation + 0x000000000007720b) 0x00007ffee9cdb190
# 34  0x000000011386a74a in  (Visual Basic for Applications + 0x000000000003a74a) 0x00007ffee9cdb2b0
# 35  0x000000011383995a in  (Visual Basic for Applications + 0x000000000000995a) 0x00007ffee9cdc330
# 36  0x0000000113850e64 in  (Visual Basic for Applications + 0x0000000000020e64) 0x00007ffee9cdc370
# 37  0x0000000108a9bb55 in  (Microsoft Excel + 0x0000000002b95b55) 0x00007ffee9cdc3f0
# 38  0x000000010839d615 in  (Microsoft Excel + 0x0000000002497615) 0x00007ffee9cdc490
# 39  0x0000000105fa9c13 in  (Microsoft Excel + 0x00000000000a3c13) 0x00007ffee9cdc520
# 40  0x0000000108a99b72 in  (Microsoft Excel + 0x0000000002b93b72) 0x00007ffee9cdc550
# 41  0x00000001088f5104 in  (Microsoft Excel + 0x00000000029ef104) 0x00007ffee9cdc580
# 42  0x00000001a2e9185d in  ( + 0x0000000000000000) 0x00007ffee9cdc710
# 43  0x000000011399c3c3 in  (Visual Basic for Applications + 0x000000000016c3c3) 0x00007ffee9cdc760
# 44  0x00000001139943eb in  (Visual Basic for Applications + 0x00000000001643eb) 0x00007ffee9cdc8c0
# 45  0x00000001b3d8839c in  ( + 0x0000000000000000) 0x00007ffee9cdca00
# 46  0x0000000111344398 in  (OLEAutomation + 0x0000000000077398) 0x00007ffee9cdca30
# 47  0x000000011134420b in  (OLEAutomation + 0x000000000007720b) 0x00007ffee9cdca50
# 48  0x000000011386a74a in  (Visual Basic for Applications + 0x000000000003a74a) 0x00007ffee9cdcb70
# 49  0x00000001139b150a in  (Visual Basic for Applications + 0x000000000018150a) 0x00007ffee9cddbf0
# 50  0x0000000108372f6a in  (Microsoft Excel + 0x000000000246cf6a) 0x00007ffee9cddc40
# 51  0x0000000108016d3f in  (Microsoft Excel + 0x0000000002110d3f) 0x00007ffee9cdddb0
# 52  0x0000000108013ebe in  (Microsoft Excel + 0x000000000210debe) 0x00007ffee9cddf00
# 53  0x000000010847c38b in  (Microsoft Excel + 0x000000000257638b) 0x00007ffee9cddfd0
# 54  0x0000000108313fe3 in  (Microsoft Excel + 0x000000000240dfe3) 0x00007ffee9ce9950
# 55  0x0000000108314710 in  (Microsoft Excel + 0x000000000240e710) 0x00007ffee9cf5f90
# 56  0x0000000107f76c7a in  (Microsoft Excel + 0x0000000002070c7a) 0x00007ffee9cf6090
# 57  0x000000010832fddd in  (Microsoft Excel + 0x0000000002429ddd) 0x00007ffee9cf65e0
# 58  0x0000000108487bd8 in  (Microsoft Excel + 0x0000000002581bd8) 0x00007ffee9cf68f0
# 59  0x00000001084497d5 in  (Microsoft Excel + 0x00000000025437d5) 0x00007ffee9cf7e50
# 60  0x0000000108ad908c in  (Microsoft Excel + 0x0000000002bd308c) 0x00007ffee9cf80d0
# 61  0x0000000107bbdb66 in  (Microsoft Excel + 0x0000000001cb7b66) 0x00007ffee9cf8180
# 62  0x0000000107c5c617 in  (Microsoft Excel + 0x0000000001d56617) 0x00007ffee9cf8230
# 63  0x00000001121e4638 in  (OfficeArt + 0x0000000000339638) 0x00007ffee9cf8280
# 64  0x000000011239c71c in  (OfficeArt + 0x00000000004f171c) 0x00007ffee9cf82b0
# 65  0x0000000107c8c831 in  (Microsoft Excel + 0x0000000001d86831) 0x00007ffee9cf82e0
# 66  0x00000001124ba554 in  (OfficeArt + 0x000000000060f554) 0x00007ffee9cf8430
# 67  0x0000000107ba3d54 in  (Microsoft Excel + 0x0000000001c9dd54) 0x00007ffee9cf8560
# 68  0x0000000107ba4151 in  (Microsoft Excel + 0x0000000001c9e151) 0x00007ffee9cf8690
# 69  0x00000001088aff13 in  (Microsoft Excel + 0x00000000029a9f13) 0x00007ffee9cf8740
# 70  0x000000010858b0d0 in  (Microsoft Excel + 0x00000000026850d0) 0x00007ffee9cf87c0
# 71  0x0000000108588258 in  (Microsoft Excel + 0x0000000002682258) 0x00007ffee9cf8c30
# 72  0x0000000106241243 in  (Microsoft Excel + 0x000000000033b243) 0x00007ffee9cf8d00
# 73  0x000000010623eadc in  (Microsoft Excel + 0x0000000000338adc) 0x00007ffee9cf8ec0
# 74  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf8f90
# 75  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf8fe0
# 76  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf9030
# 77  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf9080
# 78  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf90d0
# 79  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf9120
# 80  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf9170
# 81  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf91c0
# 82  0x00007fff333874f9 in _forwardMethod + 0x000000CC (AppKit + 0x00000000002764f9) 0x00007ffee9cf9210
# 83  0x00007fff3338de10 in -[NSWindow(NSEventRouting) _handleMouseDownEvent:isDelayedEvent:] + 0x00001332 (AppKit + 0x000000000027ce10) 0x00007ffee9cf9260
# 84  0x00007fff332f8611 in -[NSWindow(NSEventRouting) _reallySendEvent:isDelayedEvent:] + 0x00000A34 (AppKit + 0x00000000001e7611) 0x00007ffee9cf9350
# 85  0x00007fff332f79b9 in -[NSWindow(NSEventRouting) sendEvent:] + 0x0000015D (AppKit + 0x00000000001e69b9) 0x00007ffee9cf9780
# 86  0x000000010ef1dc03 in  (CocoaUI + 0x0000000000037c03) 0x00007ffee9cf97c0
# 87  0x0000000105fb827b in  (Microsoft Excel + 0x00000000000b227b) 0x00007ffee9cf9800
# 88  0x00007fff332f5d44 in -[NSApplication(NSEvent) sendEvent:] + 0x00000160 (AppKit + 0x00000000001e4d44) 0x00007ffee9cf9850
# 89  0x000000010cc639b2 in  (mbukernel + 0x00000000000059b2) 0x00007ffee9cf99c0
# 90  0x0000000105f4ddd5 in  (Microsoft Excel + 0x0000000000047dd5) 0x00007ffee9cf9a40
# 91  0x00007fff33142daf in -[NSApplication run] + 0x000002C3 (AppKit + 0x0000000000031daf) 0x00007ffee9cf9a90
# 92  0x00007fff33114b86 in _NSApplicationMain + 0x00000309 (AppKit + 0x0000000000003b86) 0x00007ffee9cf9b50
# 93  0x00007fff6fd3fcc9 in _start + 0x00000001 (libdyld.dylib + 0x000000000001acc9) 0x00007ffee9cf9ba0

x86_64 Thread State(64bit):
  __rax: 0x0000000000000000   __rbx: 0x0000600003c00520   __rcx: 0x000000000000001b   __rdx:0x0000000000000040
  __rdi: 0x000000000000011e   __rsi: 0x0000600003c00520   __rbp: 0x00007ffee9cd4360   __rsp:0x00007ffee9cd4340
  __r8: 0x0000600003c00000    __r9: 0x00000001645370e0    __r10: 0x00007fd006d10000   __r11:0x00000000000000e2
  __r12: 0x0000000000000000   __r13: 0x0000000000000000   __r14: 0x000000010af47350   __r15:0x00007fd0117db108
  __rip: 0x000000010ee6d8ac   __rflags: 0x0000000000010246   __cs: 0x000000000000002b   __fs:0x0000000000000000
  __gs: 0x0000000000000000  

Last C++ Exception @ 2020-07-22 15:39:52 +0000 (208.263564 sec before crash) which may or may not be related to the root cause of the crashing thread:
#  1  0x000000010b306879 in  (mso40ui + 0x000000000016c879) 0x0000000000000000
#  2  0x000000010b476261 in  (mso40ui + 0x00000000002dc261) 0x0000000000000000
#  3  0x000000010b4f815c in  (mso40ui + 0x000000000035e15c) 0x0000000000000000
#  4  0x000000010b475a08 in  (mso40ui + 0x00000000002dba08) 0x0000000000000000
#  5  0x0000000111b3bd29 in  (Gfx + 0x0000000000043d29) 0x0000000000000000
#  6  0x0000000111b38b54 in  (Gfx + 0x0000000000040b54) 0x0000000000000000
#  7  0x0000000111becf8f in  (Gfx + 0x00000000000f4f8f) 0x0000000000000000
#  8  0x0000000111be55c7 in  (Gfx + 0x00000000000ed5c7) 0x0000000000000000
#  9  0x0000000111be9211 in  (Gfx + 0x00000000000f1211) 0x0000000000000000
# 10  0x0000000111be7741 in  (Gfx + 0x00000000000ef741) 0x0000000000000000
# 11  0x0000000111be7704 in  (Gfx + 0x00000000000ef704) 0x0000000000000000
# 12  0x0000000111cec7af in  (Gfx + 0x00000000001f47af) 0x0000000000000000
# 13  0x0000000111cecde9 in  (Gfx + 0x00000000001f4de9) 0x0000000000000000
# 14  0x0000000111ced4ec in  (Gfx + 0x00000000001f54ec) 0x0000000000000000
# 15  0x0000000111be7d44 in  (Gfx + 0x00000000000efd44) 0x0000000000000000
# 16  0x0000000111be7bd5 in  (Gfx + 0x00000000000efbd5) 0x0000000000000000
# 17  0x0000000111be810e in  (Gfx + 0x00000000000f010e) 0x0000000000000000
# 18  0x0000000112d24480 in  (OfficeArt + 0x0000000000e79480) 0x0000000000000000
# 19  0x0000000112d25d94 in  (OfficeArt + 0x0000000000e7ad94) 0x0000000000000000
# 20  0x0000000112d2556b in  (OfficeArt + 0x0000000000e7a56b) 0x0000000000000000
# 21  0x0000000112d2523f in  (OfficeArt + 0x0000000000e7a23f) 0x0000000000000000
# 22  0x0000000112d250f1 in  (OfficeArt + 0x0000000000e7a0f1) 0x0000000000000000
# 23  0x00000001124a3140 in  (OfficeArt + 0x00000000005f8140) 0x0000000000000000
# 24  0x00000001124a3003 in  (OfficeArt + 0x00000000005f8003) 0x0000000000000000
# 25  0x0000000112492269 in  (OfficeArt + 0x00000000005e7269) 0x0000000000000000
# 26  0x000000011249197f in  (OfficeArt + 0x00000000005e697f) 0x0000000000000000
# 27  0x00000001121e3dbf in  (OfficeArt + 0x0000000000338dbf) 0x0000000000000000
# 28  0x00000001121e37c8 in  (OfficeArt + 0x00000000003387c8) 0x0000000000000000
# 29  0x00000001121e3437 in  (OfficeArt + 0x0000000000338437) 0x0000000000000000
# 30  0x00000001121e2ad2 in  (OfficeArt + 0x0000000000337ad2) 0x0000000000000000

Anything I can do to help let me know.

UPDATE:

When I commented out the Save as PDF part of the macro it completes its iterations.

The worksheets that are being saved are pre-formatted and the only thing the loop does is change which rows are hidden and the values within the report selection.

The values and data within the Report change with each iteration so I am looking to capture those in this report with each iteration.

I am thinking this link maybe the direction I need to go but what do you think?

Memory Error when exporting multiple PDFs

excel
vba
asked on Stack Overflow Jul 22, 2020 by PotterFan • edited Jul 23, 2020 by PotterFan

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0