Excel workbook with macros using 50% CPU even when idle
Firstly:
- Using excel 2010, Win 7
- All add-ons have been disabled.
- All spreadsheets used last used cell, minified to minimum row, column value
- I have an old machine in today's standards, Dell Inspiron 6400, 1.73 GHz, 3.3 Gig ram
I have a spreadsheet that when open and active it uses at least 50% of the CPU even in standby mode. If I then run other programs, it will cause the computer to overheat and shut down. I posted this issue on another forum a few months ago and the final advice was "Don't worry about it." Well, I can't do this anymore. This is becoming a problem.
At first I thought it was any book with macros. I have since convinced myself that this is not the case. Then I thought it might be the add-ons I am adding. This is also not the case. I read to minimize spreadsheets to their smallest size. It did not help. This book has an automatic opening that saves a backup, collects some data from the Internet, and does some manipulation of that data. I removed this routine completely and no effect. Another thing to note is to open another workbook, leaving that open too, and make that second workbook an active workbook, and the CPU usage will go almost zero. One final note: I have several precursors to this "final version" of this book. All of these problems do not.Somewhere I somehow added code to this "final version" that is causing this problem.
I hope someone can give me some hints as to what else to look for. I will be comparing these old backups to the current version, but it will be a long way to go. Any advice would be greatly appreciated.
source to share
As per my comment, when the Auto_Open macro was run every time the workbook was opened, it created a new connection to its internet data without dropping the previous ones. These ancient compounds must be listed. Data ► Connections ► Connections and / or Data ► Retrieve External Data ► Existing Connections.
Since I don't have the complete method and parameters of the internet connection created, I propose this kind of 'event macro' that will delete all connections whenever the workbook is closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
Dim cn As Object
For Each cn In .Connections
cn.Delete
Next cn
.Save
End With
End Sub
This macro goes into the ThisWorkbook code sheet. Note that it also saves your book; if not required, move the code to the module sheet and change it to a public macro that you can manually run.
source to share