Need help for time saving!

  • Thread starter Thread starter Jens 'Kluesi' Kluesener
  • Start date Start date
J

Jens 'Kluesi' Kluesener

Hi

Sorry for my crossposting. I found this NG after I posted the question in
microsoft.public.excel. I do my best next time.

I have got created an Excel Script that creates a Word Document. A bill.
But for only 8 bill entries the script needs to much time. For this entries
it works about 30 secounds. If you want to help me look at this page:

www.kluesener.info/excel.html

And if you find anything that saves time please tell me how I can do it
better.

Sorry for my bad english

Many Thanks

Jens
 
Jens,

A couple of tips to speed up your program.

1) ScreenUpdating = False (will run faster as screen doesn't need to be
updated.)
2) Visible = False (If you don't need it on screen)
3) With appWord.Application.ActiveDocument.Paragraphs(1).Range.Font
.Name = "Arial"
.Size = BRIEF_FONTGROESSE_ADRESSE_KLEIN
End With
(All the other settings are the default values so don't need to be set
unless you're changing them)

There are other ways to speed it up, like changing the For Next loops to Do
loops.
One of the Excel MVPs (Can't remember which one, but Google will find it)
has a web page dedicated to speeding up VBA.
Although this is specifically for Excel, most of the recommendations will
apply to Word VBA as well.

HTH
Henry
 
Hi Jens,
Item #2 of Henry's post is eliminating the code you don't need.
Page Setup type of things can be very time consuming and if
you don't need to change something then don't. Same for
your fonts. You probably got all the extra code from the macro
recorder.

Turning off Calculation would probably help quite a bit, but
then I don't call Word from Excel so you would have to be extra
careful because I can't warn you of what not to do. But when you
are finished you need to have restored Screen Updating and
Calculation before you finish, an be especially mindful of
abnormal termination.

Print Preview can turn on page break mode and that is more
time consuming. Don't know if you have that on or not, but you
might want to make sure it is off. Especially if you have to change
things like your fonts, margins..

You can save current status of Screen Updating
and Calculation in variables, and turn them off, then restore their
original settings when finished.

Just so that you can post snippets of code in newsgroups,
I would try to reduce line lengths to about 72 bytes to avoid
wrapping when you or someone else displays your code in
a newsgroup. .


For more general notes on Excel only:

More information in the following
Slow Response and Memory Problems
http://www.mvps.org/dmcritchie/excel/slowresp.htm

and also use the links to Charles Williams site, or :
Decision Models - Excel Pages - Calculation Secrets, Downloads and Tips
(it is a lot easier to use Mozilla at this site than Internet Explorer)
http://www.decisionmodels.com/optspeed.htm

You will probably gain a lot more by turning off calculation.
Between turning off screen updating and turning off calculation
you can make bad code run faster than otherwise good code.

More information on processing only the cells you want
to process by using SpecialCells see
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top