+ Reply to Thread
Results 1 to 18 of 18

Excel macro getting slower and slower.

  1. #1
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Excel macro getting slower and slower.

    I have a worksheet with a list of objectives in a column down the left and a list of users (pupils) in a row along the top (actually 2 rows merged together). The code below will automatically highlight an entire row and put a tick in every cell in that row. Or.....if the user clicks a pupil name along the top then it'll highlight that name, then if the user clicks an objective (row) it'll highlight and tick off all objectives down from the selected objective for the highlighted pupil only.

    This code runs on the SelectionChange event and works absolutely fine, however, at first the code runs very fast but what seems to happen is the actual highlighting and ticking off of cells gets slower and slower until I give up, save it, exit excel and then reopen the spreadsheet. Then it's nice and quick again for a while.

    Turning off screenupdating doesnt help either, it'll just sit at an hourglass for the same time it would have taken to update the cells and then suddenly update all the cells when the point in the code is reached where the screenupdating = true command is. It's almost as if memory limits are being reached but I thought an End Sub cleared all variables from memory.

    What am I doing wrong?

    Please Login or Register  to view this content.
    Last edited by swoop99; 06-23-2011 at 05:34 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Excel macro getting slower and slower.

    Hi

    Post a copy of your workbook
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    Sorry, can't do that. It's currently 25mb and growing and contains both copyrighted objects and sensitive data.

    Gimme a few mins and I'll put a clone of the sheet that's giving me this issue in a new workbook.
    Last edited by swoop99; 06-23-2011 at 04:11 PM.

  4. #4
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    Ok, one example sheet as requested:
    Attached Files Attached Files

  5. #5
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Excel macro getting slower and slower.

    I didin't test it, but I changed your code to remove the "GoTo" commands and replace them with looping. This is the preferred method, and it is easier to read.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    Whizbang, you sure are!

    That's soooo much faster now, thanks very much. It may still be suffering from the original problem but since it's 1000 times faster now it doesn't matter.

  7. #7
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Excel macro getting slower and slower.

    Actually the problem wasn't really looping. I just gave that to you to show you a better coding practice. The problem is that you were selecting cells inside of a SelectionChange event.

    So,when you select a cell, it would then select other cells, which prompts another SelectionChange event, which prompts more cells to be selected, which prompts more SelectionChange events, and so on until finally no more cells can be selected (you reach a stack overflow) or some condition in your code stops the selections from occurring.

    The only way to stop this is to either avoid selecting cells (which I did in the code I supplied to you, and is another good coding practice) or turn off events with
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    um....that's the 5th line of my original macro.

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Excel macro getting slower and slower.

    Oops. Ha!

    What I meant to say is...
    Uh...
    Hey look, a 100 pound cat!

    Exit stage left

    Hmm... So it was the looping afterall. I didn't know that it was so much more efficient than GoTo. I never used GoTo before, except for error trapping.

  10. #10
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    It seems I spoke too soon. When I plumb this back into the original spreadsheet it's still getting slower and slower.

  11. #11
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    New posting of macro, whizbang's code with a few odd bugs worked out:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    After some more testing, it definitely looks like a memory issue.

    The above code works very fast in a 71kb example workbook. If, however, I even have the main workbook loaded (25Mb) and not doing anything then the code in the example workbook runs slow.

  13. #13
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    Looking at windows task manager though....with just the example workbook loaded excel is using 26k. With the main workbook loaded it's using 72k.

    I don't get it.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Excel macro getting slower and slower.

    If your main workbook is 26MB then that isn't possible.

    As an aside, loops like this are pointless:
    Please Login or Register  to view this content.
    when all you need is to set them all at once:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  15. #15
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Excel macro getting slower and slower.

    Gents, thanks for all the help here but I think the problem lies elsewhere.

    Luckily, I've been keeping version histories and the version of my workbook I have from the 15th of June seems to run perfectly. Something I did on that day has caused the slowdown cos all versions after that date are slow (not just on the macro above, others as well that previously were running fine have also starting going really slow).

    However, removing the items listed in my changelog (oh yes, got a record of every change I've made to this since day 1) for the 15th doesn't fix it. Looks like I'm just going to have to go back to the June 15th version and re-apply all the dev work done for the last 9 days one by one.

    P.S. The sound of sobbing you can hear fading into the distance is me going out for a smoke.

  16. #16
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro getting slower and slower.

    I fear the problem is the triggering of the event too often.

    I came up with this:
    Please Login or Register  to view this content.
    If your workbook is 26Mb it may be worth while to remove in every sheet the rows and columns that do not contain any data. After doing so, save the file, open it again. Maybe it's size has been reduced drastically.
    Last edited by snb; 06-24-2011 at 05:41 AM.



  17. #17
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Excel macro getting slower and slower.

    Quote Originally Posted by snb View Post
    I fear the problem is the triggering of the event too often.
    If your workbook is 26Mb it may be worth while to remove in every sheet the rows and columns that do not contain any data. After doing so, save the file, open it again. Maybe it's size has been reduced drastically.
    Very nice code, snb!!!

  18. #18
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Reply to Roger

    @RG

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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