speed up vba

[Luc Benninger]

I use a small vba routine to create new documents. the macro opens an
empty document and then adds a few objects (e.g. logo image, header and
footer text boxes, etc). some text may also be added.

This whole procedure takes about 10 sec, which i find very long time for
just the few thing to do. A simular procedure in PowerPoint runs in less
than 1 sec!

Are there any hints how I can speed up my macro? Turning off
ScreenUpdating didn't help much.

Thanks a lot, Luc

 

[Greg]

Luc,

I know this doesn't answer your question, but asks one.

Why don't you create a document like one your macro creates and then
save it as a template?

 

[Luc Benninger]

Hi Greg,
Well, I probabely have to be a bit more specific.
Before the "create document" macro runs, a wizard-like dialog is
displayed where the user may choose from different options (e.g. what
brand or logo in color/in black/no logo at all). To map all these
different option combinations I would have to create too many dots. Not
to speak of the maintainability.

 

[Malcolm Smith]

Why not have these images in separate files and then load them into the
document at run time from the template?

- Malc

 

[Jonathan West]

You might try storing the various graphics within the template as Autotext
entries. It might be quicker to insert those.

Also, how are you identifying the places where these items have to be
inserted? If you are having to navigate around to get to the right place,
then mark the various locations with bookmarks instead, and insert directly
at the bookmark location. Every bookmark has a Range property that can be
used.

Finally, to be as effective as possible, you need to find out which parts of
your code are taking the longest. Add a few lines of timer code in various
places that print elapsed times to the immediate window so that you can see
what it taking the time. Then you can see what code needs to be optimized.

 

[Luc Benninger]

That's what I do right now. I have a "resource" dot with all objects in
it and use the InsertFile method to copy the needed ones into the newly
created document. But this works rather slowly. So I was hoping for any
hint to speed up my macro.

Luc

 

[Malcolm Smith]

Luc

Hmm, until we get to see your code then I have no idea. All that I
know is that it shouldn't taken ten seconds.

- Malc

 

[Luc Benninger]

Hi Jonathan

Thanks for your help. I am now working with AutoTextEntries which I
store in a seperate dot. This speeds up my macro, but unfortunately just
by about 10%. But hey, faster is faster and I think using AutoText is
the better solution anyway.

As you proposed also I measured how much time (almost) each method I
call takes to run. To my surprise the formatting of the text boxes
(which I create and insert the AutoText objects into) takes about
0.3 - 0.4 sec per box (maybe dependent on the content of the box?)! See
the formatting code below:

With shape
saveTop = .top
saveLeft = .left
.Name = id & .Name
.TextFrame.MarginLeft = 0
.TextFrame.MarginTop = 0
.TextFrame.MarginBottom = 0
.TextFrame.MarginRight = 0
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.top = saveTop
.left = saveLeft
.WrapFormat.AllowOverlap = True
.WrapFormat.Type = wdWrapNone
.TextFrame.AutoSize = False
.TextFrame.WordWrap = True
.Line.Visible = msoFalse
End With

So the time for inserting all needed AT objects sums up to several
seconds. In particular setting the RelativeHorizontalPosition property
seems to be slow. Maybe Word refreshes after each and every method call
the view of the document?? But turning off ScreenUpdating or even
setting the document window invisible does not have any effect.

As I see no way of avoiding the text box formatting, the best solution I
can imagine is showing a progress meter...

Luc

 

[Jonathan West]

You could save some textboxes with appropriate positions and sizes as
Autotext entries, then you wouldn't need to change them.