Excel 2013 "Calculation is incomplete. Recalculate before saving?"

HI mik, 

i have read your all discussion. The same problem i had. The easiest solution of this error to just save this file in macro enable book and then go to option formula tab and check on manual and un check the recalculate book before saving. Your File will be saved with out of any error.

Hope this Will Help You 

MaK-DoM_Ali

I'm venting: 2013 is hinky imo.  It goes down for almost an entire weekend(?).  I'm done complaining.

If anyone is looking for a solution in 2013, I used the following formula to conditional format, and it appears to solve the issue for me.  It doesn't work in 2010, but if you need it for 2013, try it:

=IF((LEFT(FORMULATEXT(SomeCell),1)="="), True, False)

What a pain and waste of time.

This the clue that made me realize that my "IsFormula" function had been causing issues for a long time! Thanks so much for sharing !!!

Some observations which may have something to do with the problem.

a) I had the same issue with a very simple conditional format - 4 rules setting the background colour of a column of cells depending on the text in the cell. It has always annoyed me that copying and pasting rows in this sheet does not use the original conditional format range =$A:$A) but generates 4 new conditional format rules for the pasted row with range =$A$122, and generates a new range =$A$1:$A$121;$A$123:$A$1048576 on the 4 original rules. After six months of copy/pasting rows there were an awful lot of rules. That may have been the cause of my problem.

b) Before I cleaned up I noticed that the "Stop If True" checkbox was not activated on most of the rules. That could have been the cause of my problem too.

c) The error started occurring after considerable changes to my VBA code, but none on the sheet with conditional formatting. I experience Excel 2013 running  into odd sorts of issues like this, and usually get around them by rebooting W8.

Thanks to you guys for this thread - it helped me get round my issue very quickly, as my conditional formatting was easy to clear.

For me the problem happens only in a certain sheet and it seems also related to conditional formatting which is depending on a custom Function (defined in VBA). By setting breakpoints I found out that the function is executed repeatedly every few seconds (which makes not much sense). This also causes a wrong displaying of part of the section: The numbers and text in the fields and the borders of the fields just vanish, not everywhere where the conditional formatting is, but randomly in the near area (even field which are not used to calculate the conditional formatting nor have any conditional formatting themselves). Scrolling up and down makes text reappear which is not used for calculation nor has conditional formatting applied to and the other text/numbers and borders appear for about a second and then vanish again.

To work around this weird problem I simplified the conditional formatting, so it would no longer directly depend on a custom formula (EDIT: I overlooked another conditional formatting which depended on a custom formula, so the bug still was active. Also it seems that there need to be at least 2 conditional formattings in the region to cause this bug? I solved the problem for me now by completely by having no conditional formatting depend directly on a custom VBA-formula, but rather cells which contain this formula.), but on cells which use the custom formula. Since there is was not enough space, you need to scroll to the side to see these Formulas. Now I can do the following: I scroll to the cells which do the calculation for the conditional formatting with my custom formula. I click in any cell on the screen (it can be also an empty cell). Then I click in the line where I can write in formulas (next to the "fx") and press the Enter key. I did not change anything, but this causes the continuous re-calculation to finally finish. Then I scroll back and the cells appear as they should be and I can save without the repeated Error message that I need to recalculate...

That also works the other way around: When the screen now shows the cells affected by conditional formatting and  I click in any cell on the screen (it can be also an empty cell). Then I click in the line where I can write in formulas (next to the "fx") and press the Enter key. Now the Bug turns on again and the view is messed up again and saving causes the repeated "Calculation is incomplete. Recalculate before saving?"-Error again.

Being able to reproducibly turning off and on the bug without making any changes to the workbook suggests that there is a serious bug in the way 2013 calculates conditional formatting. I read an article claiming that it would execute the conditional formatting 6 times in an experiment, in this case it is repeating it without stopping.

Please Fix!

Have very same problem. I may confirm that randomly the calculation does not finish which make the formating wrong when VBA macro is called from conditional formatting definition. I have simulated the wrong behaviour even when the VBA macros are empty functions (returning just True).

I'll just post a link to it here:

https://www.dropbox.com/s/q7tcp1s45ehwde4/Server%20Deployments.xlsm?dl=0

The bug is not consistent, but it only happens when conditional formatting is used that references custom VBA functions.  It also is limited to Excel 2013.  Excel 2010 had absolutely no problems with the conditional formatting w/ VBA code that the above spreadsheet uses.

On this spreadsheet that means only columns F & L are involved.  The VBA function for column F will highlight it yellow if more than one IP address is present.  Column L will highlight yellow if its IP address isn’t 1 higher than the IP address in column F.

To trigger the bug try changing an IP address, then press CTRL+Z to undo the change.  That action (or some other edits) may trigger the bug. You may notice that a bunch of highlighted cells suddenly are not highlighted.  If you scroll way up and then back down, they may return to being highlighted.  When you try to save when cells are not highlighted as they should be, you will get the “calculation incomplete” message.