?
Solved

Slow loading of LARGE workbood

Posted on 2013-12-16
8
Medium Priority
?
190 Views
Last Modified: 2013-12-16
Folks,
I have an extremely large worksbook with over 350 worksheets and over 5.5mb. There are tons of macros I use. It taker a long time to load. I need opinions tgo speed up loading the workbook. I'm considering placing this code when the workbook launaches:

Application.Calculation = xlCalculationManual

and place this code when the worksheet launches and closes:

Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 12

Assisted Solution

by:piattnd
piattnd earned 400 total points
ID: 39722116
Is the excel file on your local machine or is it on a network share?  Do you have it performing any actions when you first launch the excel file?  If so, does it speed things up if you trigger these via a button instead of the load?
0
 

Author Comment

by:Frank Freese
ID: 39722159
The file is on a local drive.
Nothing out of the ordinary when the workbook first launches.
I did a test and when I launch the workbook with maunal calculation the file took 2:15 to load rather than 3:21 seconds.
0
 
LVL 48

Accepted Solution

by:
Martin Liss earned 1600 total points
ID: 39722267
You could try one or both of these:

Delete all unnecessary rows at the bottom of all sheets
Save the file in xlsb format
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Frank Freese
ID: 39722339
OK Martin - I'l look at that tonight
0
 

Author Comment

by:Frank Freese
ID: 39723024
Martin,
There was a significant difference in the how file loads. I'm still building my Excel Formula and Functions workbook after all this time so you have some idea on the project you helped me with.
Thanks,
Frank
0
 

Author Closing Comment

by:Frank Freese
ID: 39723026
Thank you very much folks!
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39723031
You're welcome and I'm glad I was able to help. Which of my two suggestions helped?

Marty - MVP 2009 to 2013
0
 

Author Comment

by:Frank Freese
ID: 39723042
The xlsb
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question