Sluggish workbook
Sluggish workbook
(OP)
What I have is a fairly complex design workbook, about 3 MB in size in the Excel 2007 .xlsx format.
Depending on inputs, numerous cells return error values such as REF!, #N/A, or #VALUE!
However this is not a problem for the "bottom line" because the output cells are programmed to display various "warning" messages or blanks i.e. cell = " "
BTW the workbook contains no macros, VBA, UDF etc. I imagine it would work better with more efficient programming, but that is not an option as far as my capabilities.
Over the years, this workbook is growing increasingly complex - the problem is it has become slow to open and run on my current computer, which is not the latest but isn't ancient, either. I am reluctant to buy a new computer because of the usual unforeseen headaches. This machine has proven to be the most durable, reliable and it is fairly fast for other tasks.
My question - if I take the time to re-program to eliminate all error values, will the workbook process noticeably quicker?
Depending on inputs, numerous cells return error values such as REF!, #N/A, or #VALUE!
However this is not a problem for the "bottom line" because the output cells are programmed to display various "warning" messages or blanks i.e. cell = " "
BTW the workbook contains no macros, VBA, UDF etc. I imagine it would work better with more efficient programming, but that is not an option as far as my capabilities.
Over the years, this workbook is growing increasingly complex - the problem is it has become slow to open and run on my current computer, which is not the latest but isn't ancient, either. I am reluctant to buy a new computer because of the usual unforeseen headaches. This machine has proven to be the most durable, reliable and it is fairly fast for other tasks.
My question - if I take the time to re-program to eliminate all error values, will the workbook process noticeably quicker?





RE: Sluggish workbook
Macros may not necessarily improve speed either. The performance cost of function call overhead has to be traded against any potential gains om the code
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Sluggish workbook
RE: Sluggish workbook
I've had Excel get a corrupted cell that caused problems (slowed down opening & wouldn't run some macro's correctly). The way I fixed it was to copy & pasted everything into a new spreadsheet and it worked a lot better.
One thing I would suggest is to find out what the incorrect Refrences (REF!) are and eliminate them. If your spreadsheet is looking for another one that doesn't exist, it will be taking the time to look for the non-existing referenced spreadsheet.
RE: Sluggish workbook
The REF! errors are ocurring in cells containing LOOKUP - type formulas, not cells with invalid dependent links.
Depending on the inputs, those cells are returning REF! but that is not a detriment to the spreadsheet results.
Based on these replies, it looks to be not worth the effort to eliminate all the error values.
Years ago, this spreadsheet was about half the size and it got to the point where it would take 5 minutes to calculate, so I cleaned it up a bit and reduced the total number of designs it was capable of. (It calculates a number of beams, columns, shear walls, etc in a wood-framed building or house)
RE: Sluggish workbook
Your problem, of course, is hardly unique. My CFO from a previous job was mightily excited about getting the first IBM 386-class computer in the division because it was absurdly sluggish. Sure enough, the new computer whizzed through the spreadsheet. However, after about a month, the sheet got absurdly sluggish again, because the CFO added all the functionality he couldn't run before.
Have you looked at how much memory you have? DRAM is way cheaper than a new computer, and if you're churning on virtual memory, that can be seriously slow for all concerned.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Sluggish workbook
http://
Here is one jam-packed with suggestions:
http
=====================================
(2B)+(2B)' ?
RE: Sluggish workbook
Thanks, I will research those.
IRstuff,
My computer is WinXP, Pentium 4 CPU @ 3 GHz, only 1 gHz of RAM. Maybe that's the problem.
I pulled out a small portion of one worksheet and gutted it to eliminate all links outside that worksheet, and all defined names, just to show what I meant in previous posts.
RE: Sluggish workbook
I retired my P4 about 8 years ago... I think that XP with a large Excel spreadsheet is going to be pretty crowded with 1 GB of RAM.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Sluggish workbook
RE: Sluggish workbook
What is the correct procedure? Fortunately, I save each new version under a new name so I didn't screw up the worksheet.
RE: Sluggish workbook
RE: Sluggish workbook
Another way to speed things up is to put calculation on manual, at least for tables. You can make changes faster then when you're done recalculate.
RE: Sluggish workbook
I get a slow down on several files and once i clear all the contents of the rows and columns that seem to have no data, it speeds up greatly
RE: Sluggish workbook
- How much free space do you have on the hard drive?
- When's the last time you defragged?
- Can you reduce the number of other applications running in the background?
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
RE: Sluggish workbook
Not sure what the new file formats have done about this but some sparse math seems to have sorted it out. I just checked with the example above. In the new formats the test block was 8.08k, and 8.26k with the 65k cell full. Saving the same files into excel 97-2003 formats went to 17k and 41k respectively. Deleting the text in the errorsome cell reduced the new files back down, but the old format stayed at 41k.
RE: Sluggish workbook
This is good info!
Thanks
D23