How can I find what sub/process in excel that cause the CPU to go deadly high?

[dikken20]

Hi,

Excel 2010, Win7 64bit here.

I'm using excel specifically to run my worksheet. I barely use it for other files.
During time, adding sub here, changing some other definitions there.. adding more and more sheets to the file - all the sudden I found Excel running very high CPU and causing my Laptop heating and freezing.
I cannot use it normally to run my job. It just masses the entire laptop.
My laptop is not new, it's actually like 8 years old or so, yet, it did run just fine a couple of months ago.

I tried to remove some some of the new VBA code added during the past months, nothing changed and situation just the same
When opening the VBA window I notice that it shows "running" very fast.. which cause me conclude that something trigger the code to calculate it too often but am not sure what trigger that..

How can I find what is causing this running/calculating process that make me laptop freeze/heat?

 

[Joe4]

What kind of VBA code do you have in your workbook?
Do you have any event procedure code in the "ThisWorkbook" or any of the "Sheet" modules?
If so, what does the code look like?

 

[dikken20]

What kind of VBA code do you have in your workbook?
Do you have any event procedure code in the "ThisWorkbook" or any of the "Sheet" modules?
If so, what does the code look like?

Excellent question!
I used to have a couple of procedures there awhile ago and in order to resolve this issue I removed them all - nothing changed! still heating/freezing my laptop..

 

[Joe4]

When exactly does it heat/freeze up?
When you are just opening the file, or when you are running specific code?

How much data is in this file?
Does the file contain any links to things outside the workbook?

Do you experience the same behavior with other Excel workbooks?

 

[dikken20]

When exactly does it heat/freeze up?
When you are just opening the file, or when you are running specific code?

How much data is in this file?
Does the file contain any links to things outside the workbook?

Do you experience the same behavior with other Excel workbooks?

Short answer:
#1 Heating/Freezing begin about 1-2 minutes after opening the file.
#2 Workbook contains (so far) 15 sheets, while each sheet contains about 20,000 cells (in average). No links outside the book, only importing CSV file every 1-2 minute. More about the book and this functionality specified below.
#3 This behavior occurs only with this workbook.


General explanation of the file that will make sense (and answering #2 in specific):
Workbook contains (so far) 15 sheets, while each sheet contains about 20,000 cells (in average).

Main sheet is a raw data that been imported from local CSV file every 1-2 minute (by automatic 'Refresh' functionality of the date on that sheet),
Note: This CSV file located and imported from a local folder, yet, I use a batch file to download it from the web every 1-2 minute. I used that functionality for years without having any CPU issues.

Other sheets are using the data from the main sheet, calculating various calculations using mostly the internal Excel's formulas (mostly) and also few VBA procedures to calculate or do technical actions copy/paste data.
I do use one cell to run a "clock" in order to have a dynamic time in order to trigger some checks on the data (like running the batch that download the CSV and other data checks), that clock is using an RTD server, it's made pretty simple using DLL that Microsoft published somewhere and I used it very often without any problems.

One more thing, I do use the "Iterative calculation" otherwise I can't do the calculations.
I used to have links outside the workbook but I removed it in order to fix the issue and only thing remain outside the book is the CSV file, but that was from the very first beginning and wasn't making any CPU problem.

 

[Joe4]

I am no expert, but I would focus on two things:

1. You automated VBA code:

Main sheet is a raw data that been imported from local CSV file every 1-2 minute (by automatic 'Refresh' functionality of the date on that sheet),
Note: This CSV file located and imported from a local folder, yet, I use a batch file to download it from the web every 1-2 minute. I used that functionality for years without having any CPU issues.

If the issue does not happen on start up, and only happens after a few minutes, then that points to code that is automatically running at timed intervals as potentially being problematic.
It could be that the way your code is written is not efficient, and doesn't unload resources when it is done.

2. The age of your computer.
It could be that the laptop is beginning to die. You want want to try to run this on a different computer, and see if you experience the same issues.

 

[dikken20]

I am no expert, but I would focus on two things:

1. You automated VBA code:

If the issue does not happen on start up, and only happens after a few minutes, then that points to code that is automatically running at timed intervals as potentially being problematic.
It could be that the way your code is written is not efficient, and doesn't unload resources when it is done.

2. The age of your computer.
It could be that the laptop is beginning to die. You want want to try to run this on a different computer, and see if you experience the same issues.

Thank you Joe,
1. Since I removed the new code - I thought that problem will resolved, but it didn't. yet, This is exactly what I'm looking for - a way to find what code causing this.
2. Maybe, but other software (nor workbooks) are causing this, so I believe it has to be #1 and again, I wish there would be a way to find it.

By the way,
Every here and there when I add new data, functionality or sheets, I do save the file in a new name, meaning - older versions are saved. Yet, I tried a week ago to open a few of the files just in order to have a clue when this all begun. For a mysterious reason - they could not open.. I wonder why.

 

[Joe4]

Every here and there when I add new data, functionality or sheets, I do save the file in a new name, meaning - older versions are saved. Yet, I tried a week ago to open a few of the files just in order to have a clue when this all begun. For a mysterious reason - they could not open.. I wonder why.

That sounds like it could be a corruption issue to me, assuming that you are saving it with file extensions that match the file type you are saving to.
It could be that your file has become corrupted.

If if were me, I would really try running it from another computer, and see if that has issues to. That is the surest way to detect if the issue is with the file or the computer.

 

[dikken20]

That sounds like it could be a corruption issue to me, assuming that you are saving it with file extensions that match the file type you are saving to.
It could be that your file has become corrupted.

If if were me, I would really try running it from another computer, and see if that has issues to. That is the surest way to detect if the issue is with the file or the computer.

Running the excel file on other machine at this point is not possible due to technical issues

I really am not sure what causing the older files not to open, yet, I tried today to find what causing the heating/freezing,
I found that there're somewhere Circular reference and since I allowed iterative calculation it wasn't an issue, but I think that in some point when I added more and more data and calculations, the limit of calculation just 'killing' the laptop due to "over" endless calculations.
This is my main suspicious.

The problem is that I cannot find where the circular reference happening. the"Circular Referemces" under "Error Checking" option not showing the cells that doing that, actually the Ciruclar references is disabled.. So I just wonder how can I find where all the circular references are, searching manually in 300K cells are really not possible obviously so I really do wonder what other options I got..

 

[Joe4]

According to this document here: Circular reference in Excel: how to find, enable, use, or remove, the feature which locates circular references will not work if iterative calculations are turned on. So you will need to turn that off first to find them.