CODEWITHSUNDEEP
vbaexcelexcel-2010cpu-usage
awsmitty
awsmitty

Reputation: 121

Excel workbook with macros using 50% CPU even when idle

First:

I have a spreadsheet that when open and active it uses at least 50% of the cpu even when idle. If I then run other programs this has caused the computer to over heat and shut down. I posted this problem on a different forum a few months ago and the end advice was, "Don't worry about it". Well, I can no longer do this. It's become a problem.

At first I thought it was any workbook with macros. I have since convinced myself this is not the case. I then thought it might be add-ons that I have add. This isn't the case either. I read to minimize the spreadsheets to their smallest size. This didn't help. This workbook has an auto-open which saves a backup, gathers some data off the internet and does some manipulations with that data. I totally removed this subroutine, and no effect. Another thing to note is if I open another workbook, leaving this open too, and make this second workbook the active workbook, te cpu usage goes to nearly zero. One last thing to note is I have several precursors to this "final version" of this workbook. They all do not have this cpu problem. Somewhere, somehow, I have added some code to this "final version" that is causing this problem.

I'm hoping someone can give me some clues as to what else to look for. I will be comparing these old backups to the current version, but this is going to be the long way around. Any advice would be greatly appreciated.

Upvotes: 3

Views: 2754

Answers (1)

user4039065
user4039065

Reputation:

Subject to my earlirt comment, when the Auto_Open macro ran everytime the workbook was opened, it created a new connection to its Internet data without discarding previous ones. These ancient connection should be listed Data ► Connections ► Connections and/or Data ► Get External Data ► Existing Connections.

As I do not have the full method and parameters of the internet connection created, I'll offer this sort `` event macro that will remove 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

That macro goes into the ThisWorkbook code sheet. Note that it also saves your workbook; if this is not wanted behavior, move the code to a Module sheet and change it to a public macro that can be manually run.

Upvotes: 1

Related Questions