Macros running slower when run in succession

[indyman]

I have a macro called CalcAll that is called form a number of places. CalcAll runs 4 macros in succession doing different types of calculations (thousands). Those 4 run in less than a second. I moved additional calls to 4 chart creation subs, each creating a chart based upon the calculated data. The problem is with Charts 2 and 3. Charts 1 and 4 always run fine. If I run CalcAll where everything runs in succession (with no lines of code between the calls), I get these times for Charts 2 and 3 (3 runs):

Run in succession
Chart2 Created = 64.35938
Chart2 Created = 22.91016
Chart2 Created = 188.9766

Chart3 Created = 120.4336
Chart3 Created = 21.86719
Chart3 Created = 174.7305

But I can run the chart subs one by one from ribbon controls. And when I run them individually, I get times like this:
Manually triggered:
Chart2 Created = 0.1015625
Chart2 Created = 0.109375

Chart3 Created = 0.1015625
Chart3 Created = 8.984375E-02

As you can see the times are radically different and problematic. I have spent days trying to isolate. It is time consuming and frustrating when it takes any where from 4 to 20 minutes to run. In short, the chart code does this:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...
deletes existing chart
dynamically creates new chart
sets chart type, size, style, etc
...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

It then calls another sub to create a Common Create_Ttable below the chart. But the times above do not include the running of the Create_Ttable sub.

Anyone have any advice or things I can check?

 

[offthelip]

I have had problems with macros running slower and slower as I ran them again and again. I discovered that this was caused by a memory leak. I then did a lot of investiagtion to find out where the leak was and solved it. Excel is very poor at managing memory and so I suggest you open Task manager and monitor the memory allocated to excel as you run the macros. see if it just keep growing. It it does that is half way to solving your problem. i.e you found why it slows down but not where the leak comes from!!

 

[indyman]

As promised, here are the results of rebuilding the workbook. After rebuilding it, I beat on it running the CalcAll 95 times (from the VBE). It never failed to run without the Table_Create) in more than 1.1 seconds. But I still had 17 times where at least one of the charts showed a time of "0". So I went down the rabbit hole of building in several delays between the chart subs, and would still get some zero times. Finally I added some code to the chart subs to write a verification to one of the cells on each chart page. And that proved that the chart subs were indeed not begin skipped over. I can only assume that sometimes they run faster than the minimum precision of the built in timer - set as a Single. (?)

So then I added the ribbon XML. I added the Table_Create sub calls back in. I have added the sheet unprotect and protect back in. And after a few dozen runs, the entire process has not exceeded 2 seconds. So in conclusion, the answer does appear to be a corrupted workbook. I should also note that I believe the corruption was at the spreadsheet level. Why? Because before I started this thread, I went through the process of creating a new workbook and copying all the sheets over via the Move/Copy command. This time, however, I created new sheets and just copied any "permanent" data to those sheets. But that is just my best guess.

I will update if anything changes, but am feeling [hesitantly] confident that this is resolved. Thanks to all who have watched or contributed to this thread, especially @GWteB for your desire to keep offering suggestions and comments. Perhaps this will help someone else in the future.

 

[indyman]

@offthelip That was my first suspicion. However, while the CPU would jump 25% during the long runs, the memory always showed consistent usage and never rose over time. With the new workbook, the CPU usage is a 20% blip.

 

[GWteB]

@indyman, your're welcome and thanks for keeping us posted
Glad you made progress in solving your issue, hopefully the results persist.