Slow opening of workbook

[rob nobel]

That's real strange. Those two lines were in the top of the procedure. I
also put it back to how you wrote it as... "Application.Calculation =
CalcMode",
and it now works fine. Oh well....I now realize a bit more what that stuff
means at the top (Variables).
Thanks for all your time and advice! I'm sure it helps many learners like
myself.
Rob

Actually, CalcMode was just a variable that was holds the calculation mode when
you start your macro. I don't think that was the problem.

Did you include these two lines at the top:
Dim CalcMode As Long
CalcMode = Application.Calculation

If you forgot to set it to a nice value, that would cause an error later on.

===
The code was trying to keep things the way they were before the macro executed.
(If it was set for manual, change it back to manual. If it was automatic,
change it back.)

Dave, I put this code in and it certainly improves the speed, particularly
when filtering is involved in the macro.
I did have to change Application.Calculation = CalcMode to
Application.Calculation = xlCalculationAutomatic
I hope that's the right thing to do as my version would not accept the
former code.
Rob

I agree. Lots of my macros have this at the top:

Dim CalcMode As Long
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

and near the bottom:

Application.Calculation = CalcMode
Application.ScreenUpdating = True



rob nobel wrote:

Well, that's interesting. As I said before I was never conscious of using
page breaks and when I checked the file, it was set to off. (As it

is
with

all my files). It appears that it is turned on automatically on doing a
print preview and resets itself to off as it's always off on

reopening
the

wkbk. So I guess it may be worth adding the code to turn it off

only if
the

user is going to preview and it then slows certain macros.
Rob

The easiest way is to just turn that display of page breaks off.

Record a macro when you do it manually to see the code.
tools|options|View tab|uncheck page breaks

And I've only seen it slow it down when I added/deleted. But I

guess
if

you
changed fonts or even rowheights/columnwidths (even by clearing cells!)
that it
could slow things down, too.

Anything that would cause excel to want to figure out where to put those
page
breaks.

rob nobel wrote:

That's a good digression, Dave. I now begin to wonder how to avoid
that; by
hiding columns and rows?
I presume though, that this only slows down macros if that

function
of

adding or deleting is done within the macro?
Rob

Digressing:

I bet you do use pagebreaks. If you do a file|print preview, you'll
see
those
little dotted lines (if you have more than one printed page).

Those little dotted lines can slow down macros. When you
delete/insert
rows or
columns, excel wants to put those dotted lines in the new

spots.
If

you
do a
lot of deletes, it can take a long time for excel to do the

same
work

(over and
over and over--each time you delete a row).

But I agree with David McRitchie. I've never seen those

dotted
lines

when
I
open a file. I have to print it or print preview it first.

(But it's a good thing to remember when (different) code seems slower
for
no
apparent reason.)

rob nobel wrote:

Yes, you're both right Dave and David. No calculation

showing
in

status
bar
nor do I ever use page breaks.
Rob

Hi Rob,
I don't think this is your problem, but calculation will take
longer
if it has to determine where the page breaks are.
Tools, Options, View, Page Breaks (turned off)

The reason I don't think it is the problem is because page breaks
are off, when you first open a workbook.

Nov.

2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm