Saving time running over 5800 loops

[Ed]

Well, my routine works with only a few minor glitches. Unfortunately, it
takes over an hour to complete itself! It made 5,837 loops, so I guess
that's acceptable.

I wanted to ask about time-saving tips. If I can shave .1 or .2 seconds off
each loop, I can save a half an hour of run time.

(a) I have the main routine, during which I call up subroutines. Would it
be better to put all code in one single long module?

(b) Some of the code is straight from the Macro Recorder, so I know it's
stuffed with all kinds of parameters I don't care about. But I'm leery about
deleting lines without knowing for sure if it will have a disasterous
effect. Any guidelines for cleaning up are appreciated.

(c) I read something about clearing the Undo buffer. Would it save time to
clear this during each loop?

Thank you for your help.

Ed

 

[Jay Freedman]

Well, my routine works with only a few minor glitches.
Unfortunately, it takes over an hour to complete itself! It made
5,837 loops, so I guess that's acceptable.

I wanted to ask about time-saving tips. If I can shave .1 or .2
seconds off each loop, I can save a half an hour of run time.

(a) I have the main routine, during which I call up subroutines.
Would it be better to put all code in one single long module?

It does take a little extra time to switch from the main routine to a
subroutine and back, but it's on the order of milliseconds or less, not
tenths of a second. What you gain in being able to understand the structure
of the macro is worth this little extra time.

(b) Some of the code is straight from the Macro Recorder, so I know
it's stuffed with all kinds of parameters I don't care about. But I'm
leery about deleting lines without knowing for sure if it will have a
disasterous effect. Any guidelines for cleaning up are appreciated.

This is probably the biggest -- or second-biggest -- problem. One major
problem with recorder-generated code is that it always uses the Selection
object. You can usually replace that with a Range object that you declare
(Dim MyRange As Range). The advantage is that when you operate on a Range,
the cursor doesn't move and Word doesn't have to redraw the screen.
Sometimes you can get a similar speedup just by setting
Application.ScreenUpdating = False before the loop (and setting it to True
at the end of the macro).

Often, the biggest speed problem is a poor approach to the problem you're
trying to solve. You may be using VBA code to do something for which there's
a built-in command, or you may be using an inefficient method. There's no
way to determine that without seeing the code. If you want a critique, pack
your template into a zip file and email it to me. (My mail program throws
away "unsafe" attachments, so be sure to zip it.)

(c) I read something about clearing the Undo buffer. Would it save
time to clear this during each loop?

Probably not. The problem with the Undo buffer is that it can fill up, and
that causes an error that stops the macro. You might want to clear it, but
you won't save time that way.