Making macros run faster

[Blake]

I have a macro that can potentially take a long time to
run. I would like to know what I can do to get it to run
faster, and get an estimate of how much time would be
saved so I can determine if making the changes would be
worthwhile.

Basically the macro is the first part of a program that
automatically edits long documents composed of many
smaller documents. It automatically compiles the long
document from the many smaller documents, depending on
selections made by the user. Since the large document can
potentially include 500 smaller documents, this
compilation process can take some time, particularly on
slower computers.

The first approach I know of to make this run faster is
is to turn off screen updates while the macro runs, which
I have done (by the way, any estimates on how much time
this saves?). I know there are other similar methods, but
I am not sure what they are. For instance, something that
would be like turning the monitor off.

The second thing I know to try is to change the way
documents are compiled. Currently, for every document to
be included in the larger document its template is
opened, copied, closed, and then pasted into the larger
document. I think that using InsertFile would be much
faster (it seems to be when I use it for other things),
however each document becomes a seperate "chapter" in the
large document and needs its own footers. When I tried
the InsertFile method, the footers in the large document
were incorrect. I assume I could have resolved this (not
sure how), but opening, copying, closing, pasting worked
fine, so I stuck with it. How much time could be saved
using InsertFile vs. my current method?

Thanks for any help

Blake

 

[Malcolm Smith]

I have a macro that can potentially take a long time to
run. I would like to know what I can do to get it to run
faster, and get an estimate of how much time would be
saved so I can determine if making the changes would be
worthwhile.

Blake

It may be of use if we knew how the code was constructed.

- Malc

 

[dz]

It IS a little difficult to help without the code, but I
can at least provide you with info. on timing it to test
if you made a difference.

Dim StartTime As Single

'Place the following code at the start of the code you
want to test

StartTime = Timer

'Put the following code at the end of the code you want to
test

MsgBox "Time taken was: " & (Timer - StartTime) & "
seconds"

Be aware, though, that whatever other processes you have
running can affect the time. You will want to run this
code a few times and then take the average, as the time
given will never be quite the same.

dz

 

[Pete Bennett]

The single big tip is to use the Selection method/object as little as
possible. It takes a great deal of time (comparatively speaking) to move the
cursor and select text/objects. Instead, as much as possible see if you can
access and manipulate the range object instead.

Here's a great article that shows how VBA code can be optimized for speed
(this is for VBA itself, not accessing Word objects, but it's incredibly
useful).

http://www.microsoft.com/officedev/articles/movs101.htm

Hope that helps,

Pete.