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?

 

[GWteB]

Sometimes disabling Excel's native events (Application.EnableEvents = False) might increase performance a bit.
At the other hand, yielding to the Windows OS (VBA.DoEvents) between time consuming procedures sometimes significantly increases the performance.

 

[indyman]

I have DoEventa in other places,but not here. Is that different than VBA DoEvents? I will try the disabling of events to see what happens.

 

[GWteB]

Both are the same. EnableEvents is a member of (Excel.)Application, DoEvents is a member of VBA.
In this case it doesn't hurt to omit the parent object, sometimes not including it in the code can lead to unexpected effects (or a "member not found" run-time error).
For example, both VBA and the Range object have a member named Replace. If you explicitly specify the hierarchical path in your code, it can't go wrong (for that matter).

 

[indyman]

After trying the disable events, I still run into issues. I was even able to duplicate the issue if I just ran any chart enough times. Sometimes 3, and sometimes 10 or 15 times. I concluded something had to be hanging onto something somehow. Each of the chart subs is almost the exact same code with exception of the chart name and series ranges. Due to the copy/paste I used when creating them, of course most variable names are re-used in each. And I remembered reading in my earlier search for a solution where someone recommended setting objects to nothing to speed performance. I could not see that would be the issue, as each of these is set within each chart sub. This has not been a practice of mine in any of my previous VBA projects. Although none of them is near the scope of this project. So I went through the painstaking task of setting all of my chart and range objects = Nothing at the end of the sub. After doing that, no improvement. In fact it gets worse as time goes on. That maybe a clue.)

On the left are typical results if I executer CalcAll from the VBE. The 2 columns on the right, if it is called from one of the ribbon controls.

Sub1 = 0.1289063	Sub1 = 0.1132813	Sub1 = 0.109375
Sub2 = 0.1757813	Sub2 = 0.1757813	Sub2 = 0.171875
Sub3= 0.21875	Sub3 = 0.234375	Sub3 = 0.2265625
Sub4 = 0.1992188	Sub4 = 0.171875	Sub4 = 0.1601563
Chart1 Created = 1.171875E-02	Chart1 Created = 1.171875E-02	Chart1 Created = 0
Chart2 Created = 1.953125E-02	Chart2 Created = 1.953125E-02	Chart2 Created = 1.171875E-02
Chart3 Created = 3.90625E-03	Chart3 Created = 1.171875E-02	Chart3 Created = 0.0078125
Chart4 Created = 3.90625E-03	Chart4 Created = 3.90625E-03	Chart4 Created = 0
BudgetChart = 2.734375E-02	BudgetChart = 2.734375E-02	BudgetChart = 3.515625E-02
Total CalcAll Time = 1.070313	Total CalcAll Time = 245.1797	Total CalcAll Time = 434.082

CalcAll has no arguments. And where it is called from the ribbon, it is the last line of code except for a sheet activate. I have also seen total times of less that 2 seconds. But Excel does not update the screen for maybe a minute or two.. Anyone have other ideas?

 

[GWteB]

Not sure whether I was clear enough, but by yielding to the Windows OS I had a scheme like this in mind, without switching screen updating, calculation and xlEvents off and on in the meantime within the respective procedures. Just to be sure.

Sub CalcAll()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Sub1
        VBA.DoEvents
    Sub2
        VBA.DoEvents
    Sub3
        VBA.DoEvents
    Sub4
        VBA.DoEvents
    Chart1
        VBA.DoEvents
    Chart2
        VBA.DoEvents
    Chart3
        VBA.DoEvents
    Chart4
        VBA.DoEvents
    BudgetChart
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Interested in these kinds of mysteries I came across this article. The last contribution in particular may be of value in the search for a solution.

Pausing VBA and re-running it causes faster execution

We are using a macro for some analysis that that we want to be able to look at the process, and for a reason that is out of scope here, we are forced to use Activate and Select in the macro. Both my

stackoverflow.com

 

[indyman]

I did have DoEvents instead of VBA.DoEvents However, it did knot resolve anything. I already had the first 3 and last 3 lines in there. I read through the link you posted and even the link in it about general best practices. I do utilize all of those - except for arrays, I need to bone up on those. But this code does very little copy/paste.

I can run the CalcAll as many times as I want from the VBE window and it runs in the 1 second time frame. (Although it did take 6 seconds one time.) But any time I call it from the entry of a value in the one of the 2 ribbon editbox, it takes 45 seconds up to several minutes. However, it took 42 minutes and 22 minutes on 2 occasions today. I added the xlWait cursor to the sub. it seemed to help at first but the delays returned. The interesting thing is that when it does delay, most of the time, my timer shows a total time of the 1 second range. Sometimes it displays the ugly truth. But for some reason Excel continues to run and not return control back to me. Whether its coincidental or not, I sometime will have it return control after ALT+Tab to another app and back again. As it is running during that delay, it adds about 25% to the CPU usage. And once I ALT+Tab out of the VBE window, I cannot return to it until the "process" ends. Although I cannot easily determine the mitigating factors, if I leave and come back and wake the PC from sleep, then I can run it for 15 or 20 times with no issue, then boom. In the last hour, it closed Excel on me twice.

Additionally, I realize that I have not noted, but it might be worth mentioning is that I am protecting sheets with UserfaceOnly = True. I unprotect before writing, and Protect again at the end of the sub. I don't think that should be necessary, but I have been given the error shooing the cell is locked for editing at random times. Additionally I have a few sheets hidden using xlVeryHidden, but never change that status..

I am about to give up. I only have the one PC to test on. I might flip this over to a friend to see if he experiences the same issue. With such widely unpredictable behavior, I am beginning to think the only other thing left is to target a file corruption. I am leaning towards manually creating a new XLSM and importing all the VBA files.

 

[GWteB]

What I think is special, is that the code does not cause any problems within the VBE, but outside it does. Perhaps it has to do with the fact that your code touches certain limits of VBA. For example, I know from experience that a procedure should not be too long, so do not exceed ???? lines. This also applies to the code modules. Only that didn't give me inconsistent behavior like you and the contributor on Stackoverflow describe, Excel simply crashed right away.

And once I ALT+Tab out of the VBE window, I cannot return to it until the "process" ends.

This behaviour is odd too. The mentioned DoEvents statement should prevent that, provided it's executed on a regular interval.
Too bad it will remain a mystery for now. Maybe there are other forum members who have any idea what could be causing this issue.

 

[indyman]

I thought I would try separating the 4 chart subs into distinct modules, even though they only equate to 754 lines of code. On the 3rd time through running from the VBE, it took 36 seconds. So maybe the VBE remarks are somewhat important, or just luck.

I also tossed a quick form with a textbox and a button on a sheet to test that part outside of the ribbon. That did not run any better (3.5 minutes).

I had looked earlier about size limits as my code started growing and don't think I am approaching the "known" limits. My longest code is a userform with tons of validation at 1100 lines. The longest module is 630 lines. The longest procedure is 469 lines (which due to case statements, the longest path is maybe 1/3 of that. The largest .bas disk size is 30KB. The workbook is self contained and does not read or write anything external.

I'll try a new workbook. If that does not work, I will kick this over to a friend. If neither of those work, it has to be code related. I will keep post updates.

 

[Gokhan Aycan]

64kB for a procedure on 32 bit I guess, that's the warning I get when trying to compile code written on 64 bit.