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

Hi Rod,

Apologies for not spotting your message. Please visit my website, my email address is at the bottom of each page.

Ok, I just sent you an email!

Any chance you would share that email please ..?

Thanks.

 - Mike

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.

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).