Sheets currently terribly slow in Excel 2013 (UDF's)

Hello denpries,

Welcome to the Forum!

There can be factors that affect the speed of macro other than how the macro is written. The number and type of programs that are loaded on your system can have a large impact on execution speed.

If all of your UDFs are being recalculated whenever a cell is changed, the most likely culprit is you have made them Volatile by including the statement Application.Volatile in your macro code. This is the nature of volatile functions - they recalculate whenever any cell is changed. This can slow your calculations to crawl.

Dear Leith,

Thank you for your reply. Unfortunately, it seems to have nothing to do with the volatile statement, as it is not in my code.

That said, I would like to extend the question and problematics to my macros being more slow even if we are not talking about the UDF case.
Lets regard a macro that does the following 6 steps (followed by the timing). Note that i did some tests with application.screenupdating/enableevents and such speedups on FALSE and some on TRUE.
All processes are performed on a second workbook 2, and the macro is ran from workbook 1, e.g. (run code in workbook 1 >> opens workbook 2 >> does stuff with workbook 2)


TEST (1)
Office 2010 with processed sheet active, screenupdating, events, calculation false/manual
1 2.4296875
2 2.6796875 (+0.2)
3 4.7734375 (+2.1)
4 5.4609375 (+0.7)
5 7.59375 (+2.1)
6 7.96875 (+0.4)

TEST (2)
Office 2013 with processed sheet active, events, calculation false/manual (exactly the same test as (1), some steps much faster but step 3 always slow)
1 4,6328125
2 4,8515625 (+0.2)
3 56,09375 (+51.2)
4 56,25 (+0.2)
5 56,359375 (+0.1)
6 56,71875 (+0.4)

TEST (3)
(exactly the same test as (2), but now with screenupdating on TRUE. Weird thing: step 3 became faster?? Still most steps are at least as fast or faster than office 2010, but step 3 is megaslow ).
Office 2013, sheet active,screenupdating, screenupdating, events, calculation true/automatic
1 4,9609375
2 5,34375 (+0.4)
3 27,8828125 (+22.5)
4 28,1328125 (+0.3)
5 28,3671875 (+0.2)
6 28,7734375 (+0.4)

TEST (4)
Comparable results with TEST 1. Sheet is put to not active by performing a thisworkbook.active prior to running the process that modifies the external sheet.
Office 2013 with sheet not active, screenupdating, events, calculation false/manual
1 5,484375
2 5,7578125 (+0.3)
3 6,75 (+1.0)
4 7,140625 (+0.4)
5 7,90625 (+0.8)
6 8,984375 (+1.0)

TEST (5)
Office 2013 with sheet not active, screenupdating, events, calculation true/automatic
1 4,9765625
2 5,203125 (+0.2)
3 6,1796875 (+0.9)
4 6,5546875 (+0.4)
5 7,3203125 (+0.8)
6 9,140625 (+1.8)

As you can see, the biggest issue is in proces step 2 to 3.

This is the code used, which tests some cell in a certain column , for each row as long as there is data in that row. If needed, it makes changes.
Note, i know that this code is not optimized at all, but this topic is about comparing results of 2010 vs 2013.

Please Login or Register  to view this content.

When typing this post, i just realized that TEST2 can be made much, much shorter by activating a window other than the Excel 2013 window that contains the processed document. For instance, i clicked on skype. Within a second or two the step was complete. Without clicking on another program the test takes 50 seconds minimum. Again, application.screenupdating doesnt help (it worsens it, even?) and bringing another excel document to the foreground also helps.

What you can also see is that the cursor flickers constantly from blue circle (loading/waiting) to plus (standard mouse pointer) when the proces runs. Is this some indication?
Also note: i have an excel userform open as long as the process runs as well (it is started from the form)

Hello denpries,

I applaud your efforts in analyzing the problem. A lot have changes have taken place between Office 2010 and 2013. To see what has changed in Excel click the link below.

http://msdn.microsoft.com/en-us/library/office/ff837594%28v=office.15%29.aspx