Slow opening of workbook

[rob nobel]

In a workbook I'm constructing which is approx 650kb in size I notice that
on opening it ,that it says in the taskbar Opening ".....", etc. with the
progress meter running. It begins the process Ok showing 3 blue indicators
quickly and it then sits there for about 8-10 seconds before it opens the
file. The only thing, other than various VBA code within the sheets and
other public modules, is the following in this workbook module.
Private Sub Workbook_Open()
With Sheets("ALL A-C")
.EnableAutoFilter = True
..Protect Password:="", UserInterfaceOnly:=True
End With
End Sub
Is there a reason that can be fixed to make it open faster as I have an
earlier version of the workbook with not as many VBA procedures that opens
in less than a second (It's approx 450kb in size)?
Do the VBA procedures get calculated somehow on opening?
In fact I deleted a worksheet from the offending file that thought might be
the problem and saved it with a new name, and that one opens just as slow
without any blue indicator boxes appearing. It just sits for 8 secs and
then opens.
Rob

 

[Dave Peterson]

Sometimes you can clean up your windows temp folder and excel will respond
quicker.

Does your file sit on a network drive--or a local drive? If it's a network
drive, can you copy it to your harddrive to see the comparison?

If neither of these helped, you may want to take a look at these two sites
(Charles Williams and David McRitchie):

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm

 

[rob nobel]

Dave,
File is on a local drive (my own home PC).
Strange thing! I fixed it by unhiding rows. I had hidden some rows (from
about row 450) on one of the sheets. (Can't remember why- probably that's
where I originally thought the data was going to go to.) When I chose to
unhide them, the file took less than 2 secs to open (otherwise more than 9
secs.)
I then hid them again, but from a different spot (about 1500 rows down) and
walla.
To try and solve this, I had done a progressive delete of sheets, formulae
and VBA to be left with that sheet from which I also deleted all formulae.
Still took 7.2 secs to open. So saw that there were some hidden rows and
columns. Interestingly, hiding or unhiding the columns don't make a diff.
All a mystery to me!
Thanks for the input though, as I will check out the links. May give me
some more ideas.
Rob

 

[Dave Peterson]

I've never noticed a problem with hidden rows, but I'll be more attentive to see
if I notice a difference.

 

[rob nobel]

Yes Dave, I've never noticed it before either. I wonder, but don't know
how, if it might be linked to the fact that the data to some of the rows
that I'd hidden had been referenced in some of the VBA modules. The reason
I think that's a bit weird is that those procedures do not run until I
choose to run them. That is, they don't run on opening the file. One thing
I have observed one time is that I closed a work book and on opening it, a
message box came up telling me there was a problem with some code. That was
true, but it was not any code that was performed automatically on opening.
Rob

 

[Dave Peterson]

I don't think I've ever noticed excel's vba doing anything with code that isn't
being run. (It would be kind of amazing if it did <bg>.)

You sure you don't have any code under any worksheet_activeate or even auto_open
code.

Yes Dave, I've never noticed it before either. I wonder, but don't know
how, if it might be linked to the fact that the data to some of the rows
that I'd hidden had been referenced in some of the VBA modules. The reason
I think that's a bit weird is that those procedures do not run until I
choose to run them. That is, they don't run on opening the file. One thing
I have observed one time is that I closed a work book and on opening it, a
message box came up telling me there was a problem with some code. That was
true, but it was not any code that was performed automatically on opening.
Rob

 

[rob nobel]

I agree, that would be amazing, but I can't fathom what else would do it.
Can't believe that hiding rows from a different point could make all that
difference.
My only other thought is that some of the rows hidden do have formulas in
them.
I have found that if cells have formulas in them, the file opened
progressively faster each time I tried hiding cells from a different point
down (with less formulas each time).
This "slowness" also occurs when hiding columns, except to a much lesser
degree.
I have been in the practice of hiding rows and columns on applications, but
wonder if it might be better to use other sheets to store formulas and data
and then hide those sheets. I'll have to experiment unless you already know
the answer to that.
Rob

 

[Dave Peterson]

My answer is sometimes it might be better and sometimes it might be worse.

My general rule is to put it where it belongs--make it easiest for me.

One more thing. I know xl2002 recalculates when it opens a workbook that was
saved in a previous version. Are you running multiple versions?

You may want to test. Open excel (w/o opening your workbook). Then
Tools|options|calculation tab (and change the calculation to manual.

Then open your workbook. Maybe recalcs are slowing you down.

I know that when I have lots of raw data (no formulas at all) and apply
data|Filter|autofilter and filter on a column, excel will slow down--I think
it's trying to recalculate non-existent =subtotal()'s (which'll change depending

I agree, that would be amazing, but I can't fathom what else would do it.
Can't believe that hiding rows from a different point could make all that
difference.
My only other thought is that some of the rows hidden do have formulas in
them.
I have found that if cells have formulas in them, the file opened
progressively faster each time I tried hiding cells from a different point
down (with less formulas each time).
This "slowness" also occurs when hiding columns, except to a much lesser
degree.
I have been in the practice of hiding rows and columns on applications, but
wonder if it might be better to use other sheets to store formulas and data
and then hide those sheets. I'll have to experiment unless you already know
the answer to that.
Rob

 

[rob nobel]

I'm running Excel 2000 on which it is being created, Dave. If I do as you
suggested, to set to manual, it makes no difference to the speed it opens
at, (which is now just under 2 seconds). Even if I rehide the rows back at
the point where it causes it to open very slowly ie. (nearly 10 seconds).
That is, manual or auto calc does not make any noticeable difference to this
particular workbook.
When I set the worksheet with the rows hidden that cause the slowness, the
slowness occurs only in the process of opening (that is, the worksheet does
not show....just a blank white appearance on screen. It does not seem to be
processing calculation during the initual opening process, as once the
workbook opens, there is a momentary delay (with the egg-timer), which I
presume is when calculation occurs.
Rob

 

[Dave Peterson]

I don't recall seeing this (but I will keep an eye out).

You don't see a "calculating" (or something like that) in the statusbar??

(I bet not or you would have mentioned it.)

I'm running Excel 2000 on which it is being created, Dave. If I do as you
suggested, to set to manual, it makes no difference to the speed it opens
at, (which is now just under 2 seconds). Even if I rehide the rows back at
the point where it causes it to open very slowly ie. (nearly 10 seconds).
That is, manual or auto calc does not make any noticeable difference to this
particular workbook.
When I set the worksheet with the rows hidden that cause the slowness, the
slowness occurs only in the process of opening (that is, the worksheet does
not show....just a blank white appearance on screen. It does not seem to be
processing calculation during the initual opening process, as once the
workbook opens, there is a momentary delay (with the egg-timer), which I
presume is when calculation occurs.
Rob

 

[David McRitchie]

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.

 

[rob nobel]

Yes, you're both right Dave and David. No calculation showing in status bar
nor do I ever use page breaks.
Rob

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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

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

 

[rob nobel]

Yes, Thanks for that Dave. Probably a good bit of code to speed things up
generally.
Rob

 

[rob nobel]

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

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.

2001]

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

 

[Dave Peterson]

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

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov.
2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm