Slow loading excel file

[scouse]

I have a 2mb file which is taking an age to open.

I amended the file last month and added a couple of charts (I also took out roughly the same amount of charts) but it only increased the file by .2mb so that doesnt seem to be an issue. Even when I copy and paste special and take out all formulae etc it still is taking a couple of minutes to open. As this file is shared with our district managers it is causing great annoyance.......

I have done the following in an attempt to make the file open faster and am now out of ideas:

Took out all cell formatting in unused cells in all the sheets
Deleted all hidden sheets
Copy and pasted the file into a new file and resaved
Took out all old links to other files
Took out all VBA code which was no longer used

Any further advice would be much appreciated!

Scouse

 

[nightcrawler23]

Is there any code in Workbooks.Open event?

 

[ISY]

Hallo
How long does the F9 key to recalculate I mean
How many formulas a Matrix?
If there are many Pivot?
If you try to move the location of a tab takes time?

These are the first questions without being able to view the file

 

[scouse]

When I have a look in the vbaproject window it has a lot of class modules (all of which are .xll or .xla and require a password to open them).These dont seem to be related to the existing file (is this normal??).

The class module related to the file rcp_template.xls contains all the names of the sheets but there is no code in them

Scouse

 

[nightcrawler23]

When I have a look in the vbaproject window it has a lot of class modules (all of which are .xll or .xla and require a password to open them).These dont seem to be related to the existing file (is this normal??).

Scouse

This should be normal depending on what add-ins u have installed

 

[scouse]

Hallo
How long does the F9 key to recalculate I mean
How many formulas a Matrix?
If there are many Pivot?
If you try to move the location of a tab takes time?

These are the first questions without being able to view the file

How many formulas a matrix?? Sorry dont understand
Recalculate runs fine - the file is updated with new figures each month from an external file but this runs the same as always.
No pivots
Tabs moving fine

The only slowness is in opening the file.

Apologies cant upload file due to admin restrictions at work.

 

[ISY]

Hallo

quote: How many formulas a matrix?? Sorry dont understand


These types of formulas on different sheets in large areas of reference

= SUMPRODUCT(prices,holdings)

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

quote: Tabs moving fine
In the presence of links between the sheets, if you try to move tabs from one position to another run of the recalculationYou could try to open it without the macro-enabled, try running the following code before opening the file:

Sub Disable_Events()
    Application.EnableEvents = False
End Sub

 

[scouse]

Hallo

quote: How many formulas a matrix?? Sorry dont understand


These types of formulas on different sheets in large areas of reference

= SUMPRODUCT(prices,holdings)

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

quote: Tabs moving fine
In the presence of links between the sheets, if you try to move tabs from one position to another run of the recalculationYou could try to open it without the macro-enabled, try running the following code before opening the file:

Sub Disable_Events()
    Application.EnableEvents = False
End Sub

Nope, no Array formula in this workbook. The file is basically a monthly report so the biggest formula in it is a simple SUM for adding monthly data together....the amount of links between sheets is minimal roughly 300 in total for the 40 worksheets.

Put in the code (thanks for that) and still takes the same amount of time.

The confusing thing is that when I take out all the formula/links etc to produce the final report for users the file still takes as long despite the fact that the size of the file is 25% smaller. And the same file 3 months ago was larger and doesnt have the same problem.

Also it doesnt take long to save.....so not related to the server where the file is saved.

One thing that happens is when the file opens I cant click anywhere in the sheets or access the menubar unless I first minimise the file and then unminimise. Its almost like its frozen when its opened.

 

[ISY]

Hallo

If you have for ... INSTRUMENTS Options Calculation Iterations
See if anything changes by changing the values

Otherwise, try testing with a file used to remove the formulas by putting in place values ​​such as a page at a time to determine if some formula creates the problem.

A later!