Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduce "Calculating Cells" ?
I've been working with Excel for about 8 months now, and i'm writing my own
VBA code and producing sheets that I thought would be impossible 3 months ago. Now as I make more and more complex forms/charts/tables to accomplish more difficult things i'm faced with, I see often "Calculating Cells" and have to wait. Sometimes its a lengthy weight depending on the formulas. I've also started using loops in macros to deal with lots of data, and each time the macro does something it has to recalculate cells. In some instances i've set it to not calculate automatically, and only do so when told to, but this is inconvient on some sheets. To give an example. One thing i've been doing is having a small Sumproduct table, rather than a pivot table, I find it easier to use, and is more accurate when automatically updated (the "Visit" column doesn't jump from D to E) Unfortunatly, a couple of Sumproducts, or 1 lengthly one and its stuck calculating. I'm fully aware that the main problem with this may be the work server not being powerful enough, either though the processing power allocated to my terminal, or by the complexity of what i'm asking. But was wondering if anyone knew of some simple (or even complex) ways to effectivily reduce the Calculating time of spreadsheets. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduce "Calculating Cells" ?
I don't think it has anything to do with the server--it's the speed of your pc.
In your macros, you can turn calculations to manual at the beginning and toggle it back to what it was when you started the code at the end. Depending on what you're doing, turning off screenupdating and making sure you're in Normal view may speed things up, too (and those little dotted lines that indicate page breaks can slow things down, too.) Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Manually, you can toggle the calculation mode, do all the data entry, then toggle it back to automatic. And make sure that your array formulas (including =sumproduct()) don't use a gigantic number of rows -- if you don't need them. And here are a couple of sites that discuss slow performance. The first is by Charles Williams and the second by David McRitchie: http://www.decisionmodels.com http://www.mvps.org/dmcritchie/excel/slowresp.htm PaulW wrote: I've been working with Excel for about 8 months now, and i'm writing my own VBA code and producing sheets that I thought would be impossible 3 months ago. Now as I make more and more complex forms/charts/tables to accomplish more difficult things i'm faced with, I see often "Calculating Cells" and have to wait. Sometimes its a lengthy weight depending on the formulas. I've also started using loops in macros to deal with lots of data, and each time the macro does something it has to recalculate cells. In some instances i've set it to not calculate automatically, and only do so when told to, but this is inconvient on some sheets. To give an example. One thing i've been doing is having a small Sumproduct table, rather than a pivot table, I find it easier to use, and is more accurate when automatically updated (the "Visit" column doesn't jump from D to E) Unfortunatly, a couple of Sumproducts, or 1 lengthly one and its stuck calculating. I'm fully aware that the main problem with this may be the work server not being powerful enough, either though the processing power allocated to my terminal, or by the complexity of what i'm asking. But was wondering if anyone knew of some simple (or even complex) ways to effectivily reduce the Calculating time of spreadsheets. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduce "Calculating Cells" ?
Thanks for the help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting new data before "calculating cells" is done??? | Excel Discussion (Misc queries) | |||
How do I reduce the number of lines in a spreadsheet? | New Users to Excel | |||
How to Reduce Spreadsheet Size and Speed | Excel Worksheet Functions | |||
Any tips to reduce excel workbook size? | Excel Discussion (Misc queries) | |||
Reduce columns and rows count? | Excel Worksheet Functions |