Speed up workbook
Posted by Steve W on April 21, 2001 10:10 AM
As my workbook continues to get larger it slows down. I currently have 6 sheets and 3 userforms, one userform is a splash screen, ones for printing and ones used to increase prices on one of the sheets. I also have about 300 autoshapes that are controlled by about 6 VBa functions. I'm know adding a lot of pictures(diagrams) which I wrote some code to change ones linked range to display the others(one at a time based on selecting a cell). Each picture adds about 300kb in size.Before the pictures my workbook was under 2 Mb, now my workbook is about 5.5Mb in size and opens slow and calculates slow.
Does anyone have any tips for me to speed things up
(calculating and opening workbook)
thanks steve
Posted by Dave Hawley on April 21, 2001 10:28 AM
Hi Steve
1. Save the workbook as one version only. Don't save as multiple versions unless needed.
2. Export ALL modules and Userforms to your Hard drive.
3. Open a new Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then Copy the sheet to the new Workbook. Then save your new Workbook.
4. Do the same for all Worsheets, but each time you copy a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then try again.
5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.
6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.
By following these steps I have succeeded in reducing a Workbook by 75%
Dave
OzGrid Business Applications
Posted by Dave Hawley on April 21, 2001 11:09 AM
Here are some intresting links for you Steve. Some may apply ?
http://support.microsoft.com/support/kb/articles/Q186/3/69.ASP
http://archive.baarns.com/excel/develop/vbaperfm.asp
Dave
OzGrid Business Applications
Posted by Steve W on April 21, 2001 11:56 AM
: Hi Steve : 1. Save the workbook as one version only. Don't save as multiple versions unless needed. : 2. Export ALL modules and Userforms to your Hard drive. : 3. Open a new Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then Copy the sheet to the new Workbook. Then save your new Workbook. : 4. Do the same for all Worsheets, but each time you copy a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then try again. : 5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms. : 6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation. : : By following these steps I have succeeded in reducing a Workbook by 75% : : Dave
Here are some intresting links for you Steve. Some may apply ? http://support.microsoft.com/support/kb/articles/Q186/3/69.ASP http://archive.baarns.com/excel/develop/vbaperfm.asp
Dave
Does the problem with Jpeg apply to office 2000 and I'm copying pictures from adobe photoshop and pasting them into excel. Is this the best way to insert pictures or is there a better way to take up less space.
thanks steve
Posted by Mark W. on April 21, 2001 12:25 PM
MS Recommendations for Optimizing Worksheets for Fastest Calculation
See http://support.microsoft.com/support/kb/articles/Q72/6/22.ASP?LN=EN-US&SD=gn&FR=0&qry=recalculation&rnk=17&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97
Posted by Dave Hawley on April 21, 2001 1:46 PM
Steve, I would think it would not apply to Office 2000 as according to the article SR2 for 97 corrects the problem.
Although your file size is big, this may not be the reason for slow saving and opening. Each time you save or open Excel recalutates all formulas (unless you have it set otherwise). So it may be that you have used formulas incorrectly. Did you try my recomendations for reducing file size in my first post?
As far as formulas are concerned too many links and/or arrays are the biggest culprits. That is why I suggested using Pivot Tables and/or the database formulas in their place.
Dave
OzGrid Business Applications
Posted by Steve W on April 21, 2001 3:55 PM
Dave can you tell me if this looks like a corrupt file
After I copied the first sheet the file size was around 400K, 2nd 800K, 3rd 1.9M, 4th went down to 1.5M, 5th 2.6M, 6th 3.2M.
I think there might be something wrong.
What do you think?
steve
Posted by Dave Hawley on April 22, 2001 2:22 AM
Re: Dave can you tell me if this looks like a corrupt file
After I copied the first sheet the file size was around 400K, 2nd 800K, 3rd 1.9M, 4th went down to 1.5M, 5th 2.6M, 6th 3.2M. I think there might be something wrong. What do you think? steve
Steve, it does seem strange that the file size would go down when inserting another sheet. Copying all sheets will eliminate the possibility of a corrupt Workbook, but doesn't eliminate the possibility of a corrupt Worksheet. On each Worsheet in the NEW workbook go to the last row of data, highlight the entire row and holding down your Ctrl+Shift key push the Down arrow. This will highlight all rows below your data. No go to Edit>Clear>All. Do the same on each sheet then save again.
DaveOzGrid Business Applications
