Challenge with Automatic Calculation

[Michael R]

My piece of code runs under manual calculation mode. When it is done I want
the whole lot to be recalculated.
Towards the end of my code I have the following statements:

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

End Sub

The problem:
Watching the worksheet status in the right bottom corner of the screen I see
that the lot is calculated twice (the "Calculating xx%" runs from 0 to 100
twice) which is a bit of a nuisance since it takes quite a while.
How can I get it to calculate only once?

 

[Gary''s Student]

Rather than go to Automatic, try:

Sub MichaelR()
Application.CalculateFullRebuild
End Sub

 

[Michael R]

Thank you very much, works fine - and calculates only once.

 

[Michael R]

Unfortunately I have now discovered a challenge with the ...Rebuild:

When I save, close and re-open the workbook I get a "Excel found unreadable
content in Workbook". When I agree to having it repaired some of my formulae
are being replaced by "=#N/A". The odd thing is that the original (identical)
formulae above and below the "problem-cell" are OK. The workbook is too large
to do a comprehensive check whether these N/As are the only resulting issues.

I have tried to narrow the problem down and found that it appears to be the
Rebuild which results in this situation. I have tried
"Application.CalculateFull" and "Application.Calculate" and neither of them
creates the problem.

Do you have any clue what the root-cause might be - and how to fix it?

Thanks for your help,
Michael