CalculateFullRebuild vs CalculateFull

[Anthony]

Hi

Can someone please advise the main difference between
CalculateFullRebuild and CalculateFull, and when they should (or
should not) be used.

I have a large workbook, and have set the OnKey event for the F9 key
to run my own 'FullCalc' VBA procedure (see below).

I basically try and recalculate the workbook firstly, and then check
the Calculation State.
- If it is still 'Pending' (i.e 'Calculate' appears in the Status
Bar), then i do a Full Calc.
- If the Calculation State is still 'Pending' after a 'Full Calc', if
in Excel 2003, i do a Full Calc with Rebuild.

However, a Full Calc with Rebuild can take over a 1min.

Is it necessary to do a CalculateFullRebuild all the time the user
clicks F9 when the formula dependency limit is reached, or is my
procedure overkill and can i get away with just doing CalculateFull?

------------------------------------------------------------------------------------------------------------------

Private Sub FullCalc()

If Left(Application.Version, 1) = "8" Then '97
SendKeys "%^{F9}", True
Application.SendKeys "%^{F9}"
DoEvents
Else
If Left(Application.Version, 1) = "9" Then '2K
Application.CalculateFull
Else
If Left(Application.Version, 2) >= "10" Then 'XP or later
Calculate
If Application.CalculationState = 2 Then
Application.CalculateFull
If Application.CalculationState = 2 Then
Application.CalculateFullRebuild
End If
End If
End If
End If
End If

Application.Calculation = xlCalculationAutomatic

End Sub

 

[Charles Williams]

Hi Anthony,

Assuming that your Calculate in Status Bar is caused by too many
dependencies rather than one of the other causes there is no point in using
CalculateFull or CalculateFullrebuild: just an ordinary F9 Calculate will
calculate correctly.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Anthony]

Hi Anthony,

Assuming that your Calculate in Status Bar is caused by too many
dependencies rather than one of the other causes there is no point in using
CalculateFull or CalculateFullrebuild: just an ordinary F9 Calculate will
calculate correctly.

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com














- Show quoted text -

Thanks Charles for your response.

I am assuming, that after i run the other forms of calculation, if
'Calculate' is still appearing in my Status Bar, than i must have
reached the dependency limit, therefore i run the CalculateFullRebuild
as a last resort.

Are you suggesting that i dont have to do that, and if the dependency
limit has been reached, a standard Calculate will still calculate my
workbook OK?

Anthony

 

[Charles Williams]

Yes, when Excel (versions before 2007) hits one of the dependency limits it
sets a switch so that all subsequent F9 recalculations are done as
CalculateFull anyway, which will give the correct answer.

You only need to use CalculateFullRebuild if the calc chain/dependency tree
has been corrupted so that some parts of the workbook are not calculating
correctly. This is very rare and probably less likely to happen when the
dependency limit has been reached because excel does not try to create and
maintain the dirty cells dependencies.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Hi Anthony,

Assuming that your Calculate in Status Bar is caused by too many
dependencies rather than one of the other causes there is no point in
using
CalculateFull or CalculateFullrebuild: just an ordinary F9 Calculate will
calculate correctly.

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com














- Show quoted text -

Thanks Charles for your response.

I am assuming, that after i run the other forms of calculation, if
'Calculate' is still appearing in my Status Bar, than i must have
reached the dependency limit, therefore i run the CalculateFullRebuild
as a last resort.

Are you suggesting that i dont have to do that, and if the dependency
limit has been reached, a standard Calculate will still calculate my
workbook OK?

Anthony