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

starl

Administrator
Joined
Aug 16, 2002
Messages
6,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,182
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

Administrator
Joined
Aug 16, 2002
Messages
6,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hey RunningMan :biggrin: (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

Administrator
Joined
Aug 16, 2002
Messages
6,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,205
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Dec 23, 2017
Messages
2,514
Office Version
  1. 2010
Platform
  1. Windows
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

Administrator
Joined
Aug 16, 2002
Messages
6,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
'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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,276
Messages
5,853,032
Members
431,543
Latest member
mojamoja

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top