Excel - slow start up and shut down

[Bluejay07]

I have an excel application that has approx. 80 worksheets, approx. 50 forms and several code modules.
The files size is approx. 9 MB.

The application works fine except that is it really slow on start up and shut down.
There is code in the Workbook_Activate and Workbook_Open procedures.

To determine when the code has finished processing in the workbook_open event, I have added

Application.StatusBar = "Ready"

It takes almost no time at all for this message to appear so we know that it's not start up code that is affecting things.

After this message appears, the application still takes upwards to 2-3 minutes before it's responsive. The only way we know when the sheet is ready to use is by clicking a cell and when Excel is ready to be used, the cell is selected.
We have used excel 2003/windows xp and excel 2010/windows 7 and the wait is longer in 2010.

As part of our testing, we have moved or copied all code and worksheets to a new workbook in case the workbook was starting to become corrupt. That didn't seem to help.

The workbook does have a lot of controls (text box, combo box, list box, butons, check box, etc.) on the various sheets and forms.
Our best guess at this time is that excel is loading all of the workbook controls into memory on start up and releasing that memory when closing the workbook. The forms seem to load the controls as the form itself is loaded.

During start up and regular usage, we only display one worksheet at a time (unless we physically unhide a sheet). All other sheets are not visible.

Does anyone know which processes may be running that could slow this down or if there is a way to load the controls as the sheet is loaded rather that all at once at start up or if there is a setting that would affect this.

In case anyone is wondering, setting the controls visible or enabled property to false doesn't help either.

Another reason we think it's the controls that is slowing things down is if we try to open two versions of the workbook, an out of memory error appears. I believe this is excel's internal memory check. Both versions are still usable but it's very sluggish.

We have converted several sheets to forms although there are many sheets we just can't do that with.

Any help would be appreciated.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[SkipVought]

faq707-4105

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bluejay07]

Hi Skip,

Thanks for the link to the faq.
We use many techniques such as with statements and toggle events to make things run faster.

Here's one example:

Public Sub ToggleAllEvents(blnState As Boolean)
    With Application
        If Not blnState Then .Calculation = xlCalculationManual
        If blnState Then .Calculation = xlCalculationAutomatic
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        .CutCopyMode = blnState
        .StatusBar = blnState
    End With
End Sub

As mentioned, we do not have any issues using the application.
Accessing sheets, database calls and database display is quick.

It's only on start up and shut down where the slow down occurs.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[SkipVought]

So what happens in the Workbook_Open event?

When calculation is changed from manual to automatic, then I believe that a full APPLICATION calculate takes place (all open workbooks in this instance of Excel)



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bluejay07]

In the workbook open, we set toggle all events to false then we verify a setting saved in a cell to determine which sheet should be displayed as the start up sheet (different access points). Either start up sheets has certain functions that are run which either update a database value or retrieve a database value. We set all events to true and display a status bar message. This process takes a few seconds. After that it takes more time before the application is responsive.

It just commented out the calculation and that didn't seem to much of a difference.

If at first you don't succeed, then sky diving wasn't meant for you!