Weird Calculation Issue

[Guy Hoffman]

I have a huge workbook(50megs) with over 180 sheets that uses VBA cod
to rewrite formulas on certain "consolidation" sheets based on whic
sheets the user selects in a VBA Form. I have noticed that at times
certain higher-level formulas (i.e., formulas that are dependent o
values in many thousands of cells will not update, even if I press F9.
I have to press [Shift]+[Control]+[Alt]+F9 in order for the workbook t
properly re-calcualte.

I cannot recall where I got the idea to try [Shift]+[Control]+[Alt]+F
much less why its necessary.

I'm thinking it may have something to do with the priority an
sequence excel uses for calculation.

Anybody seen this before?

Regards,

G

 

[David McRitchie]

Hi Guy,
If you have formulas that are dependent on something that
Excel does not recognize as being changed such as cell
color or a cell that is not shown in the formula then a normal
calculation will not fix it. Also as you say if you have a lot
of dependent calculations then there is a limit that Excel
stops doing a quick calculation.
Shortcut Keys in Excel
http://www.mvps.org/dmcritchie/excel/shortx2k.htm
--

 

[Charles Williams]

Hi Guy,

F9 recalculates only those formulae that Excel thinks have had a precedent
changed
Ctrl/alt/F9 does a full calculation (calculates all formulae in the
workbook) (usually slower than F9)
Shift/Ctrl/Alt/F9 rebuilds the dependency tree and then does a full
calculation. (usually much slower than ctrl/alt/f9) (only avaialable in
Excel 2002 and 2003)

There are some very rare cases where the workbook contains corrupted
dependency trees and so using shift/ctrl/alt/F9 is required, but mostly you
get the same result faster with ctrl/alt/f9.

In most cases F9 does the job, except when Excel has got confused about
precedents (primary culprit is user-defined functions).

for more details see
http://www.Decisionmodels.com/calcsecretsd.htm and associated pages.


hth
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Guy Hoffman]

Thanks gentlemen.

I am sure its because I have confused excel with a bunch of poo
programming in VBA using user defined functions.

G

 

[Charles Williams]

Hi Guy,

If you are using VBA UDFS there are examples of how to avoid common
calculation problems at
http://www.decisionModels.com/calcsecretsj.htm

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm