+ Reply to Thread
Results 1 to 5 of 5

Macros running much slower than they had been

  1. #1
    Registered User
    Join Date
    04-30-2004
    Posts
    78

    Macros running much slower than they had been

    A number of years ago I wrote a database update program using Excel 2000. The program contains numerous relatively short macros each of which runs anywhere from 2 to 30 seconds. We have used the program several times a week for all these years with no problems. About 3 weeks ago, I showed a friend the basics of debugging a macro (step into, step through, setting breakpoints..) using this program. Shortly thereafter we noticed the macros (all macros in the workbook) were running much slower than before. We have older copies of this program and they still run at the rapid speed. We tried copying all instructions in the macro from the older rapid running program and pasted them into the corresponding macro in the slower running program. The macro then ran at the slower speed. Could I have inadvertently done something in the debugging process that made the macros run more slowly? Any help would be appreciated!
    Last edited by nebb; 12-20-2014 at 06:59 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macros running much slower than they had been

    There are some steps that we take to speed up macros.

    Did you perhaps modify one of these lines?

    I use two macros

    Optimise to speed up my macros

    Deoptimise to slow them down again

    I will attach optimise and explain what it does, perhaps it might point you in the right direction.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 12-18-2014 at 09:43 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-30-2004
    Posts
    78

    Re: Macros running much slower than they had been

    To mehmetcik - Thanks very much for your suggestions. The first one you suggested "Application.ScreenUpdating = False" did the job. The macro is now running at its previous rapid speed. One last question. I've never used this instruction before, either true or false. The macro ran fast for years up until recently. Would you venture a guess at what I might have inadvertently changed to have resulted in the need for now including this Application instruction.

  4. #4
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Macros running much slower than they had been

    nebb,

    for instance, you may have had more volatile formulas in your workbook which are recalculated (if calculation is not turned to manual) every time a value is put in through VBA.
    Otherwise, always try to minimize the interaction there's between VBA and Excel. For instance, to select and activate cells is not a step that is needed and as an example, but there are many things to think of. To use arrays may prove very useful to optimise speed. Another example, if you have a script that is set out to delete entire rows containing specific values in cells, it is faster if you first unify the rows/cells to a range and then delete them after the loop, instead of deleting them one at a time. Less interaction.. Just a few thoughts.

    Here's a good article from Jimmy Penna that may be of interest to you.

    Happy holidays,
    berlan

  5. #5
    Registered User
    Join Date
    04-30-2004
    Posts
    78

    Re: Macros running much slower than they had been

    Thanks Berlan and a special thanks to Mehmetcik. for helping resolve this issue............nebb

+ 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. [SOLVED] Suddenly slower running code...
    By fgruhlke in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-10-2014, 11:20 PM
  2. Excel macro getting slower and slower.
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-24-2011, 06:41 AM
  3. Macros running much slower in 2007/Win7
    By surfengine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2010, 05:51 PM
  4. VBA routine gets slower and slower on each iteration of the main loop
    By whitespaces in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-15-2009, 03:29 AM
  5. Excel 97 macros run slower on a Pentium 4/Win Xp environment
    By Inver in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2005, 03:08 AM

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