Mystery Crashing of Excel After Saving Code

[Ark68]

After I edit a module in Microsoft Visual Basic for Applications, I routinely save the code. But within a few seconds of saving, Excel crashes, restarts, and opens the last saved version of the document that was being worked on at the time of the crash. Is there a means of checking what might be causing this crash? The code isn't (shouldn't be) running to be the culprit. It only happens after I run my code and it programmably stops in this module to allow me to step though, edit and save changes.

 

[Ark68]

I read this behaviour can sometimes be caused by a corrupted file, so I created a new file. Copied my worksheets, module and userform codes and saved it. But I ams still getting the same behaviour. I've restarted the computer, same problem. This is exhibited on two different computers. It happens whether I save from the editor, or from Excel.

I removed, what I believe to be all, the new code I wrote from the point where I wasn't experiencing this, and still the same. I run my project (which worked flawlessly until I built in new code), it stops at programmed breakpoint in one of the modules I am testing, I make some edits to that module's code, save it (from the VBA editor) and within a few seconds, Excel crashes. Between the time I save and it crashes, I have no control in either the editor or Excel proper. There are no error messages, no spinning cursor, it just abruptly closes and reopens to the excel file 'last saved by user".

 

[Ark68]

For what it's worth, the module below could be the cause. If I stop the code before reaching this module I do not get this crashing. As soon as I include it, anything after this module is executed will result in a crash when I try and save.

Is there anything out of the ordinary that might be causing some instability? I am trying so sort a dynamic range of data based on a custom sort list. If there is another way to do it, I'm willing to try it to rule this code out as being a problem.

Sub pda_sort1()
    Dim CList(1 To 9) As String
    'Stop
    With ws_master
        nrec = Application.WorksheetFunction.CountA(.Range("C12:C37"))
        CList(1) = "DT"
        CList(2) = "DR"
        CList(3) = "FT"
        CList(4) = "FR"
        CList(5) = "CT"
        CList(6) = "TR"
        CList(7) = "GM"
        CList(8) = "GS"
        CList(9) = "SE"
        Application.AddCustomList ListArray:=CList
        .Range("A13:G" & nrec + 13).Sort Key1:=Range("B13"), order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, key2:=Range("F13"), order2:=xlAscending
        Application.DeleteCustomList Application.CustomListCount
    End With
    
End Sub

 

[Logit]

Posting your workbook (without confidential information/data included) would be very helpful.

This forum doesn't have a means of posting workbooks. You'll need to use a CLOUD website and post the link.

 

[Ark68]

Hi Logit, thanks for acknowledging my struggle.
I would love to be able to provide the full workbook, but it is 1000s of lines of code, with references to several different data source workbooks. To try and adjust it to neutral non confidential data would likely result in further errors, so it wouldn't be an easy task. If it's simply to look at the code, and not run it, to see the structure, I could provide the primary workbook, but it won't work without the sensitive supporting worksheets. Thats why I suggested maybe a different way of custom sorting instead of trying to see how the code appearingly causing the problem is messed up.

If I can provide anything else, I will certianly try to share I can.

 

[Logit]

Here is a sheer guess on my part ... it might not change anything.

Edits highlighted in yellow and comments :

The above edits allowed the macro to "run" here without error. However I don't know if it actually sorts anything now.

 

[Ark68]

Hi Logit, I appreciate your time having looked deeper into a possible solution. Sadly, these changes (although still provided the sort accurately), Excel still crashed after attempting to save it after the code stopped. I realized that in those instances where Excel was crashing after this code was executed did not save any changes I may have made prior to saving. So the crashing was happening before the file was actually saved to the drive. Any changes to the code have to made before my project is run.

The only changes were the declaration of ws_master as worksheet ... that is a publicly declared value set early on in my project. The underscore after xlGuess was already there.

I was pretty excited when I realized a few things.
1) the worksheets was protected (although the cells of the affected range being sorted are supposed to be unlocked). I added code to unprotect and re - protect the worksheet prior to sorting. No go.
2) The range of sorting (Range("A13:G" & nrec + 13)) was actually one row too many (that extra row would be empty of any data) so I though maybe that was messing around with the sort. Changed it, but still no love.

From a functional side, this is providing the results. There are no errors being presented, or involuntary breaks. Everything runs smoothly. The exception is when an attempt is made to save the document. If this module is bypassed, no problems saving. Run through this module and saving causes issue. Although it appears to be working, who knows what problems are compounding only to manifest later on. I really hope someone can help identify the issue, or perhaps share another method of sorting the range.

 

[Ark68]

I wasn't able to get it editted in time, but here's the code as it resides now ...

Sub pda_sort1()
    Dim CList(1 To 9) As String
    Dim nrec As Long
    'Stop
    With ws_master
        .Unprotect
        nrec = Application.WorksheetFunction.CountA(.Range("C12:C37"))
        CList(1) = "DT"
        CList(2) = "DR"
        CList(3) = "FT"
        CList(4) = "FR"
        CList(5) = "CT"
        CList(6) = "TR"
        CList(7) = "GM"
        CList(8) = "GS"
        CList(9) = "SE"
        Application.AddCustomList ListArray:=CList
        .Range("A13:G" & nrec + 12).Sort Key1:=.Range("B13"), order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, key2:=.Range("F13"), order2:=xlAscending
        Application.DeleteCustomList Application.CustomListCount
        .Protect
    End With
    
End Sub

 

[Logit]

When you protect or unprotect a sheet, there is a password involved. Are you leaving the password empty / blank
or is there an existing password that you are not including in the macro code ?

Another thought ... what happens if you wait to save the workbook changes until just before closing the workbook vs after
each change in the data ?

 

[Ark68]

When you protect or unprotect a sheet, there is a password involved. Are you leaving the password empty / blank
or is there an existing password that you are not including in the macro code ?

Another thought ... what happens if you wait to save the workbook changes until just before closing the workbook vs after
each change in the data ?

All good questions ...
Yes, the worksheet is protected, but not password protected.
Even if I run through the whole project (which concludes shortly after this offending module) without stopping (there are no errors to break the code involuntarily), or making any edits, attempting to save the workbook, whether from Excel proper, or the VBA editor. Emit that module, it all works. No whether something is happening before or after that module that is contributing ... without seeing the workbook in action, would just be a guess.