VBE says there's code running but I can't find the code - then Excel crashes

[starl]

I've got a project (client confidential, cannot share) that, after the code runs, will eventually crash (eventually means less than 5 minutes or even 10 minutes later, it just sitting there doing nothing).
I was in the VBE once before a crash, but I wasn't able to do anything (VBE was 'locked up') when I noticed it said it was running. So I hit the Pause button - and was able to resume work in the VBE. So I tried to figure out what might be running (press Run, do a manual break to try and get a line of code to light up) - but nothing worked. Note that this is not always the case. Most of the time, I can go into the VBE and edit, but it will still crash.

Anyone have experience with such a thing or have ideas to help me track down the culprit? My code is running to completion, including the clearing of various objects/variables. I've even taken out bits of code to trace a specific bit - but with the crashing being random, I haven't traced anything. And it's not my system - the client has the issue too.

In case what the project does might important:
Program opens two other workbooks and puts their data in memory.
Loop through the data in memory, logic comparisons, generating 2 more arrays of results
Results are placed in corresponding workbooks. One has conditional formatting added to it, the other has data in memory looped, and cells in the workbook Color Filled.
Any errors are placed in a listobject in the original workbook.

I just let Excel sit 5 minutes or so, after running the program. I thought I had figured it out (rebuilt a listobject) - but nope, Excel crashed and re-opened.
Update: I thought the issue might be the Conditional Formatting, so took it out of the code. Still crashed, eventually.

 

[ranman256]

I occasionally get a pc that whatever flavor of Office it has, it would crash.
Yet the same code would run on another pc with the exact office version and not crash. This glitch in the Matrix was never found why.
It may be conditional formatting like you said.

 

[starl]

Hey RunningMan (love your avatar - an oldie movie, but a goodie!)
Actually, I'm in process of testing another theory - session variables. When the workbook is opened, I set some variables to some class objects that I use in the various programs in the workbook. I've currently turned that off and it's been over 40 minutes since the last crash. Though, I have no idea why those variables would cause a crash...

 

[starl]

But it crashed again once I ran the full code, so took out the CF and it still crashed. So, back to narrowing things down...
Though, getting rid of the session variables did help. Though, those classes are just helpers.. one is to organize various values (eg. sheet names), the other is Properties (eg to a specific sheet) and the 3rd is functions I use in all my projects (eg get the last row from a sheet).

 

[johnnyL]

@starl I doubt you will get much help if you cant supply some code to look at. You can change the data to protect the innocent if you feel that you need to.

What I am saying is we will probably not be able to guess what the problem is.

It could be the size of the files being loaded, code that is burning up the memory, etc.

 

[offthelip]

I have had problems with memory leaks with excel , so I suggest you check how much memory excel is using when opening your workbook and running your code. I solved my problem by putting a bit of code in to specifically kill variables/objects at the end of each loop

 

[starl]

Thanks all for the comments. @johnnyL - I have over 2500 lines of code. I agree that posting code is the way to get help with it - but that would be a bit much for the forum. And the fact that the code ran smoothly and completely and the crash would happen 10 minutes later, it wasn't actually the code. Not 100%

@offthelip - cleaning up is a top Best Coding Practice and one I follow pretty well. Not saying I haven't had some dirty projects that would actually leave the workbook visible in the VBE when said workbook was actually closed. Still working on getting those updated (old projects).

But - I'm here to say that I'm 99.9% sure I figured out the culprit (Excel has yet to crash after running the code several times and sitting for an hour)! One thing I had not mentioned - I have a custom sort list. And, like a good little programmer, I delete the custom list when the program is done. BUT! - I did not clear the Sort filter first (or at all, actually). And, after some random research, I found that to be an issue in Excel for decades, one which they've done nothing about (not even an error message). Excel just crashes (after some time, so it's hard to put the pieces together).

So, if Excel crashes some time after your program runs and you've created a custom sort order list in your code, you need to clear the Sort THEN delete the custom list:

'not my actual code
Activesheet.Parent.Sort.SortFields.Clear 'clear the sort
Application.DeleteCustomList Application.CustomListCount 'then delete the last list, which happens to be mine