Why My VBA Project Crashes When Trying To Save

[Ark68]

I realize this is likely a tough question to answer given the fact that I can't provide all my code (it's a large worksheet bvased "application") or the file to play with due to it's use of network drives and a variety of external data ssources. So I come looking for just a general answer. My sincere apologies in advance.

After my code runs, everything seems in order until I save the workbook. When I save the workbook, Excel shuts down. No error messages, it just shuts down then restarts with that file reopened (but not a recovered version ... a version without an corrections made prior to it's attempt to be saved).

Are there any diagnostic tools or tricks to determine what might be causing this? Are there any common circumstances which usually result in this kind of behaviour, and how would I check to see if these are contributors?

 

[Ark68]

Hi all.
Rory, again ... hoping that was the easy solution. But no. That made no difference sadly.
I hope my 2-3 year project doesn't get grounded by this crazy problem. I can't do anything past once the code runs through. It either crashes as I try to save (including any changes or data) or within a random period of idle.

        .Activate
        .Unprotect
        wisADD = Application.WorksheetFunction.Match("ADD", .Columns(1), 0)
        i = 13
        Do While IsNumeric(.Cells(i, 2)) = False
            .Cells(i, 18).Value = Format(.Cells(i, 6), "h:mm")
            .Cells(i, 18).Font.Color = vbBlue
            .Cells(i, 18).Font.Size = 6
            i = i + 1
        Loop
        Set PdaSortRng = ws_master.Range("A13:R" & wisADD - 1)
        PdaSortRng.Sort key1:=ws_master.Range("R13"), order1:=xlAscending, Header:=xlNo
        
        wisADD = Application.WorksheetFunction.Match("ADD", .Columns(1), 0)

In case it matters, the sort column R contains time values.

 

[Ark68]

In diagnosing, I removed all cell validations, any calculations, changed the sort key among other things and in all cases sorting still crashed Excel.

The solution I had to utilize to prevent the crashing from the sorting, was to transfer the sort range to another worksheet, sort the range there, and send it back to the original worksheet. This prevented the crashes. Obviously something going on with that worksheet ws_master that sort can't work with.

 

[RoryA]

Do you have other code that sorts using the worksheet.sort object, rather than the range.sort mehod?

 

[Ark68]

Hi Rory, no. Not that I am certain of.
Just for testing, at one point I did include

ActiveSheet.Sort.SortFields.Clear

just in case I had some conflicting sort whatevers going on.

 

[RoryA]

You'd actually want ws_master.Sort.SortFields.Clear

 

[Ark68]

Hmmmm. Maybe it be worth testing with this change? Working on another problem now and can return to testing that soon. Lost some time with this crashing thing and need to catch up.
Thanks for your continued support Rory.

 

[Ark68]

Hi Rory,
I finally had a chance to test. This was indeed the problem. I must have had a custom sort somewhere that I had forgotten, and the code correction you suggested in post 15 appears have stopped the crashing.
Thank you for seeing this through to a solution!!!