Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow loading workbook
I've been asked to overhaul a workbook of some fifty worksheets with dozens
of macros and a messy user interface. I plan to convert the macros, which appear to have been created using the macro recorder, to VBA code, implement named and dynamic ranges wherever applicable and redesign the interface into something more visually appealing as well as functional. The biggest problem from a user standpoint is the workbook takes more than a minute to open. That's pretty much unaccepatble, but I'm not really sure of what can be done to significantly improve the load time. Any help, direction, or suggestions will be greatly appreciated. Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow loading workbook
You could do one of the following
- Turn off calculation and see how fast it opens - Check the WOrkbook_Open event to see what it does. I'm sure someone else has another idea. HTH, Barb Reinhardt "Ken Warthen" wrote: I've been asked to overhaul a workbook of some fifty worksheets with dozens of macros and a messy user interface. I plan to convert the macros, which appear to have been created using the macro recorder, to VBA code, implement named and dynamic ranges wherever applicable and redesign the interface into something more visually appealing as well as functional. The biggest problem from a user standpoint is the workbook takes more than a minute to open. That's pretty much unaccepatble, but I'm not really sure of what can be done to significantly improve the load time. Any help, direction, or suggestions will be greatly appreciated. Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow loading workbook
I once inherited a workbook (in Excel 2003) where a user had put a zero in
cell IV65536 in a few of the sheets and that took an eternity to open. Delete any unused rows & columns... Then I'd look at your formulas - worksheets that we have that are typically slow have large numbers of vlookups and sumifs. If you have a macro that hard codes this area on demand you can make the whole think slicker. Sam "Ken Warthen" wrote: I've been asked to overhaul a workbook of some fifty worksheets with dozens of macros and a messy user interface. I plan to convert the macros, which appear to have been created using the macro recorder, to VBA code, implement named and dynamic ranges wherever applicable and redesign the interface into something more visually appealing as well as functional. The biggest problem from a user standpoint is the workbook takes more than a minute to open. That's pretty much unaccepatble, but I'm not really sure of what can be done to significantly improve the load time. Any help, direction, or suggestions will be greatly appreciated. Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow loading workbook
I changed calculation options from automatic to manual, deleted all macros
and code modules, saved the workbook, closed it, and then reopened it. It still took a minute-twenty seconds to open. That seems unreasonable even for a workbook with fifty worksheets. Other complex workbooks on my computer do not take any where near as long to open. Any other ideas on what might be bogging this one down? Ken "Sam Wilson" wrote: I once inherited a workbook (in Excel 2003) where a user had put a zero in cell IV65536 in a few of the sheets and that took an eternity to open. Delete any unused rows & columns... Then I'd look at your formulas - worksheets that we have that are typically slow have large numbers of vlookups and sumifs. If you have a macro that hard codes this area on demand you can make the whole think slicker. Sam "Ken Warthen" wrote: I've been asked to overhaul a workbook of some fifty worksheets with dozens of macros and a messy user interface. I plan to convert the macros, which appear to have been created using the macro recorder, to VBA code, implement named and dynamic ranges wherever applicable and redesign the interface into something more visually appealing as well as functional. The biggest problem from a user standpoint is the workbook takes more than a minute to open. That's pretty much unaccepatble, but I'm not really sure of what can be done to significantly improve the load time. Any help, direction, or suggestions will be greatly appreciated. Ken |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow loading workbook
Use Explore (right click start, select explore) to check the size of the spreadsheet file. If the spreadsheet file size is 5MB or larger, it will take 20-30 seconds to load. If the file is large but you know the 50 worksheets only have limited data, do the follow: This will take several minutes, but may help you in the long run. Do this command on all worksheet (50 times). Start on the first worksheet, then press Ctrl+End this takes you to the last cell used on the worksheet. If you find a cell way out of bounds, delete all of the unnecessary columns & rows. You could have data misplaced in wayward cells. Press Ctrl+Home to back cell A1. And go to next worksheet. When finished with all 50 worksheets, resave the spreadsheet and check the file size again. If the file is significantly smaller you have solve your problem. If not, you could copy & paste all of the sheets into a new workbook and check the size. HTH, -- Data Hog "Ken Warthen" wrote: I changed calculation options from automatic to manual, deleted all macros and code modules, saved the workbook, closed it, and then reopened it. It still took a minute-twenty seconds to open. That seems unreasonable even for a workbook with fifty worksheets. Other complex workbooks on my computer do not take any where near as long to open. Any other ideas on what might be bogging this one down? Ken "Sam Wilson" wrote: I once inherited a workbook (in Excel 2003) where a user had put a zero in cell IV65536 in a few of the sheets and that took an eternity to open. Delete any unused rows & columns... Then I'd look at your formulas - worksheets that we have that are typically slow have large numbers of vlookups and sumifs. If you have a macro that hard codes this area on demand you can make the whole think slicker. Sam "Ken Warthen" wrote: I've been asked to overhaul a workbook of some fifty worksheets with dozens of macros and a messy user interface. I plan to convert the macros, which appear to have been created using the macro recorder, to VBA code, implement named and dynamic ranges wherever applicable and redesign the interface into something more visually appealing as well as functional. The biggest problem from a user standpoint is the workbook takes more than a minute to open. That's pretty much unaccepatble, but I'm not really sure of what can be done to significantly improve the load time. Any help, direction, or suggestions will be greatly appreciated. Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addins-Slow loading of each individual Addin using Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 one network file running (loading and saving) slow | Excel Discussion (Misc queries) | |||
Shared Workbook not loading | Excel Discussion (Misc queries) | |||
Is anyone else noting the slow speed of 2007 loading files? | Excel Discussion (Misc queries) | |||
Excel slow at loading text heavy workbooks | Excel Programming |