Reduce "Calculating Cells" ?

[Guest]

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]

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

 

[Guest]

Thanks for the help.