Challenge with Automatic Calculation

M

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?
 
G

Gary''s Student

Rather than go to Automatic, try:

Sub MichaelR()
Application.CalculateFullRebuild
End Sub
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top