Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    What's slowing my macros down? (2003 SP2)

    A couple of my macros are recently going snail-slow and I don't know why! Can someone help me shed some light?

    I have a slew of data editing and cleaning macros that I use all day, every day at work as I munge and manipulate data. There are two that -- maybe about a month ago -- slowed down incredibly, from a fraction of a sec to 30 seconds or more. They are both fairly simple routines that loop through every row in a set of data. I turn ScreenUpdating off, but that doesn't seem to help.

    One example: In my ToDo list, I have a macro that hides or shows all rows where Status is "completed". There are 575 rows (not many!!) and 550 of them are "completed". The macro basically checks every cell in the Status column and switches the row's .hidden property between True and False if the cell's value = "completed". The odd thing about this one is that it usually runs quick the first time, then subsequent runs bog down. I don't know rhyme or reason. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I suspect that changes in my desktop or network environment might be a cause, but I don't know where to look. This example macro resides in the ToDo List file itself but the other 'problem' macro is in my Personal.xls file which I've been building and toting along with me for years, with 102 macros and 7 custom functions in 9 modules.

    Mahalo for your suggestions!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Hard to say, but here are some suggestions (you've already applied the first one): Speeding Up Slow Excel VBA Code. Efficient VBA Code/Macros.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Thanks Hans,

    There are some good code suggestions in there! None have worked yet though, with my speed problem... and I had high hopes for the EnableEvents property... [img]/forums/images/smilies/sad.gif[/img]

    JohnJ

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Try looking at the Processes tab of Task Manager during the execution of a slow macro. Is Excel itself hogging the processor, or another process?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Your question is kind of like my saying to you, "I have a blue car that won't start, can you tell me why?" It is very difficult to say what the problem might be without seeing the macro, preferably in a workbook where it shows the problem when it is run. Can you upload an example workbook with dummy data that demonstrates the problem?
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Good suggestion Hans, but Excel was not the processor hog at any time. Outlook won that race. I killed processes and re-tried the macro, but nothing changed Excel's relative position.

    Thank you Legare, you're right of course. I hesitated at first post to attach an example because the occurrence is not regular; I know that I need to do more scientific experimentation, isolating and ruling out possibilities. Anyway, here's what else I've learned: the HideOrShowCompleted() macro works quick until I print the sheet, then it operates s-l-o-o-o-o-w-l-y. An odd occurrence as I modified my ToDo list for public post: even simple edit/replaces on the 575 rows took more than a second, and the screen blinked feverishly as if many things were happening. Hmm.

    Attached is my ToDo list, with macros intact and content edited for public post. My car IS blue. Please fix it?

    Aloha,

    John Jacobson

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    oh brother - example attached <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What's slowing my macros down? (2003 SP2)

    I can reproduce the problem - initially, macro execution is instantaneous, but after doing a print preview, it is horribly slow. Try using AutoFilter instead: click in any cell of the table, and select Data | Filter | AutoFilter to display the autofilter arrows. The code can then be simplified to

    Sub HideOrShowCompleted()
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    If wsh.FilterMode = True Then
    wsh.ShowAllData
    Else
    wsh.Range("A2").AutoFilter Field:=6, Criteria1:="<>Completed"
    End If
    End Sub

    This runs quickly on my PC, even after a print preview.

  9. #9
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Mahalo Hans, that's very useful code! John like. Any suggestions on what might be causing the slowdown on the other macro, or how to circumvent it? I have two other macros that used to run fast and now run mind-numbingly slow, even on first run. And although I have ScreenUpdating turned off, I still see the changes appear (slowly) on screen. The autofilter method won't help me here, these macros are for cleaning raw data downloads.

    Any ideas where to look? What made the subject macro run slow after print or print preview?

    JohnJ

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Sorry, no idea.

    Added later: since the slowdown only occurs after a print or print preview, a workaround is to run the macros before printing...

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    What view setting are you using (View, normal or Print preview, or...)?

    Might also help to change to a different default printer?

    Finally, Excel 2003 may cause a recalc when you hide rows. Maybe setting calc to manual helps?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What's slowing my macros down? (2003 SP2)

    John,
    Since your problem only occurs after print previewing, I would guess that page breaks are the problem. Try adding:
    <code>ActiveSheet.DisplayPageBreaks = False</code>
    to the start of the code and
    <code>ActiveSheet.DisplayPageBreaks = True</code>
    to the end and see if that helps at all.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    I like the customized buttons you have on the spread shhet; could you please enlighten me on how to do this?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What's slowing my macros down? (2003 SP2)

    They are not buttons, but AutoShapes. Display the Drawing toolbar, and click AutoShapes. The shapes John used are from the Basic Shapes and Block Arrows categories.
    You can set colors, add text and assign a macro to an AutoShape by right-clicking it.

  15. #15
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What's slowing my macros down? (2003 SP2)

    Jazzymoves, Hans is right on. Any picture or AutoShape object that you add to a worksheet, you can assign a macro to. Try adding one and right-clicking it, and see what your options are.

    Thanks Jan, I appreciate the suggestions although changing view and printer settings had no effect. I didn't play around with manual calculation yet, because...

    Rory, many mahalos. I don't understand why, but setting DisplayPageBreaks = False worked! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> The macro is now instantaneous before and after print/print preview. I'd bet big $$ that DisplayPageBreaks is a cause of the odd occurrence I mentioned in the 20-Aug-06 12:11 post above.

    Folks, here's an interesting observation: the slowdown in my two data-cleaning macros only occurred when the ToDo list was open. Now that I've added the DisplayPageBreaks switch to the macro in that file, having the ToDo list open affects the operation of one of the cleaning macros, and the other now runs smooth. Hmm. (btw, I don't intend to resolve this mystery, I can work around it.)

    Mahalo all!

Posting Permissions

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