Solved

Slow loading of LARGE workbood

Posted on 2013-12-16
8
145 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
  • 5
  • 2
8 Comments
 
LVL 12

Assisted Solution

by:piattnd
piattnd earned 100 total points
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Martin Liss earned 400 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
OK Martin - I'l look at that tonight
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Frank Freese
Comment Utility
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
Comment Utility
Thank you very much folks!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
The xlsb
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now