CPU Usage 100%

[MikeDBMan]

I have one Sub that seems to keep bumping my CPU Usage to 100% and basically freezes everything up. I can't see anything unusual about the code but without uploading all the code, is there something I could do to alleviate this situation. My process works only with 2 files. One looks up data from the other repeatedly. And sheets are added to the other file but nothing I haven't done many times before without this problem. Suggestions?

 

[Derek Brown]

My first thought would be that re-calculation is taking place 'automatically' - and Excel will normally re-calculate all open workbooks (http://support.microsoft.com/kb/214395).
Perhaps you have a number of 'volatile' functions that are not helping (http://www.decisionmodels.com/calcsecretsi.htm).
Are you controlling the calculations in VBA, so that it does not take place everytime a cell value changes? Have a look at http://msdn.microsoft.com/en-us/library/bb687891.aspx to learn what will trigger recalculation.

 

[CaliKidd]

Mike, have you stepped through the code to see which line(s) are causing the CPU usage to skyrocket?

 

[MikeDBMan]

An IT guy at my work solved the problem. It seems it had nothing to do with Excel per se. He suggested propping up the PC and putting a fan underneath it. That solved the issue. He said he would contact the laptop vendor as I was not the first person who had had this exact same situation. So I guess it wasn't the code causing the problem.

 

[MikeDBMan]

I had definitely turned off recalc and several other things during the running of the sub. I still find it curious that only this one sub would cause the cpu hit consistently. I have had intermittent problems over the months I've had this computer but never pinpointed it to an overheating problem.

 

[MikeDBMan]

I don't think this is related but something else that I have happen quite often is that when running some code, Excel will become "(Not Responding)". In other words the title bar of the window has the same txt as before but with the words "(Not Responding)" added. And the screen shades to a white color. You can still see all the cells but the screen is like it's in a snowstorm, white everywhere. Worst of all you can't interrupt Excel to stop the code from executing. You have basically two choices: Wait for a long time until it finally comes back. Ctrl-Alt-Delete and stop Excel. CPU usage does not go above 60% during this event typically but I can't make it not happen and I seem to make it happen frequently. Right now all it is doing is going through 60,000 records in a table and adding txt to 4 columns based on the data in one of the other columns. Nothing hard by any means. It has been sitting in "snow mode" for about 10 minutes now. If I had saved the file where the VBA resides I'd just kill it but since I forgot to save and don't wish to rely on the autosave, I will wait. Do you think autosave might be the problem?

 

[CaliKidd]

In regards to your (Not Responding) issue, I had a similar problem when working with large sets of data. It was related to memory. Even though I have 4GB of memory, apparently xl2007 only is able to use 2GB.

The way I resolved it was to break the operation into chunks and to loop through it. I also displayed a progress bar to provide some visual feedback. This helps provide assurance the PC has not locked up. I had to experiment with various chunk sizes to optimize performance vs display. Monitor your memory usage as the code runs to see how much memory Excel is using to execute your code.