+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    Dear all,

    I am a newbe on this forum, but far from newbe in Excel.
    However, I face a problem I cannot tackle on my own.

    I have a certain UDF which can be used, of course, in the cells. Think of it as somethin as: =myudf(A1;C4;"test") and it returns in the cell "success!" followed by the sum of both given cells. Of course, in reality, the UDF is quite more complex, but that does not really matter here I think. In my experience with Excel 2010, the UDF was only called when A1 or C4 was changed, or just before saving the file, and things like that. If i had multiple cells with this UDF, and all were pointing to different cells, only those UDFS who point to changed cells were updated. I could easily verify this by increasing a global variable with 1 everytime the UDF was called.

    However, in Office 2013, as soon as i change 1 cell, all UDF's recalculate, even the ones not pointing towards that particular cell.

    Also, in general, I experience that Excel 2013 is much, much slower when it comes to automatic cell-wise mutations than 2010: if i fill a large range of rows with a for loop it takes much, much longer. Is this a known problem?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: 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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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)
    Last edited by denpries; 04-07-2014 at 04:17 PM.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    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

  5. #5
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    Leith, i know the link. Unfortunately it doesnt show me any entry point to solve the problem.

    Unfortunately a lot of people experience slower performance of excel/vba (google leads to many results) but i havr yet to find a solution from someone that solved the issue

    Verstuurd vanaf mijn HUAWEI P6-U06 met Tapatalk

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

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


    Hi denpries !

    It's obvious, when writing directly in a worksheet using a loop, desactivating screen update is faster …
    But in some case, fastest is to work with an array variable in memory.

    Regards !

  7. #7
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    Quote Originally Posted by Marc L View Post

    Hi denpries !

    It's obvious, when writing directly in a worksheet using a loop, desactivating screen update is faster …
    But in some case, fastest is to work with an array variable in memory.

    Regards !

    Usually i would say: true!
    However, as you can see in test 2 vs test 3: screenupdating OFF makes it even worse! Funny, ey?

    Also, the problem described in the topic concerns a comparison between office 2010 and 2013 and any change in e.g. screenupdating should lead to approximately the same performance increase / drop. As you can see however, office 2013 performs much worse as long as the processed sheet is active. And i dont know why this is the case.

  8. #8
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    Mhhh. Not much hope to find a solution anymore atm

    Verstuurd vanaf mijn HUAWEI P6-U06 met Tapatalk

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

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


    As I wrote :

    Quote Originally Posted by Marc L View Post
    But in some case, fastest is to work with an array variable in memory.

  10. #10
    Registered User
    Join Date
    06-27-2010
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    That is true , and i know you can write the resulting array at once to the excel sheet, but it doesnt solve the question what causes the large difference in performance beteeen 2010 and 2013

    Verstuurd vanaf mijn HUAWEI P6-U06 met Tapatalk

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copying sheets from a closed workbook - Excel 2013 crashes Sheets.Add
    By perducci in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-10-2014, 09:03 PM
  2. Excel 2013 slow and stalling when using filters
    By jdb4370 in forum Excel General
    Replies: 3
    Last Post: 10-30-2013, 11:40 AM
  3. Replies: 0
    Last Post: 09-20-2013, 11:15 AM
  4. Terribly slow excelsheet!
    By pigeonhome in forum Excel General
    Replies: 5
    Last Post: 04-21-2013, 09:09 PM
  5. Excel Crashed Terribly
    By Kiran in forum Excel General
    Replies: 5
    Last Post: 08-16-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1