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

Hello Jan,

I changed the UDF to what you suggested, and am still getting the "Calc is incomplete..." error.

One thing to note --- if I run any vba subroutine, or refresh a pivot table, the workbook allows me to save without the error arising.

Microsoft absolutely HAS TO FIX THIS.

Going back to Win 7 and Excel 2010.

Sigh.

Thanks anyway.

[Seriously!]

 - Mike

I'm not convinced Excel 2013 will not support UDF calls in conditional formatting formulas, for that I would like to see a test workbook.

UDF's are very sensitive to how they are programmed.  A badly written UDF can break the calculation train, even when just called from worksheets cells.

Hence my question: what does the UDF look like. If you cannot share it, fair enough, but that also means we cannot troubleshoot the problem.

Hi

I guess this function is the one causing the problem, since it is calculated again and again without any change affecting the cells it is used in:

Function EngStrg2Number(EngStr As String, UnitStr As String) As Double

    Dim Pos As Integer

    Dim EngFactor As Double

    Dim Prefix As String

    EngStrg2Number = 0

    While EngStrg2Number = 0 And Len(EngStr) > 0

        Pos = InStrRev(EngStr, UnitStr, -1, 1)

        If Pos > 1 Then

            Prefix = Mid(EngStr, Pos - 1, 1)

            Select Case Prefix

               Case "P"

                    EngFactor = 1E+15

               Case "T"

                    EngFactor = 1000000000#

               Case "G"

                    EngFactor = 1000000000

               Case "M"

                    EngFactor = 1000000

               Case "k"

                    EngFactor = 1000

               Case "d"

                    EngFactor = 0.1

               Case "c"

                    EngFactor = 0.01

               Case "m"

                    EngFactor = 0.001

               Case "u"

                    EngFactor = 0.000001

               Case "µ" 'Unicode U+00B5

                    EngFactor = 0.000001

               Case "µ" 'Unicode U+03BC

                    EngFactor = 0.000001

               Case "n"

                    EngFactor = 0.000000001

               Case "p"

                    EngFactor = 0.000000000001

               Case Else

                    EngFactor = 1

            End Select

            EngStrg2Number = Val(EngStr) * EngFactor

        Else

            EngStrg2Number = Val(EngStr) 'Error

        End If

        EngStr = Mid(EngStr, 2) 'Maybe there are characters at the start... kill them if it is really 0, it stays 0 until we killed the complete string

    Wend

End Function

What the function does is quite obvious: it extracts a number from a string and includes "m" (=mili=1e-3), "n" (=nano=1e-9), "k"(=kilo=1e3) ... as a factor. IT also deletes leading characters, which confuse the "Val" function and return 0.  Maybe I should rather search for a RegExpression like

[0-9]+.?[0-9]*

for the number and then with another regular expression for the Unit and extract the character before it... That should be a more clean way, I guess? I am also nit sure about the two Micros (Greek and Science) which may or may not be used as input, whether Excel really handles them differently?

@Jan Z: I guess I misunderstood you. I know now what you mean. As a workaround you could make a second sheet for each sheet and there fill in your custom formula to determine whether the cell in the other sheet is locked and copy it all over the sheet and then refer with the conditional formatting to the other Sheet like:

=Sheet1_Formating!A1=True

Applied to $A$1:$AZ$99 in Sheet1

and then you should have the correct effect.

You could then also hide that sheet or even make a Macro to create such sheets and hide them...

Although helpful in the sense that it properly analyses the problem, I cannot work around it so easily, so I agree with the last line of the post: Please Fix!

I use a simple trick that used to work like a charm to make fields that can be filled in stand out. I created a VBA function that returns true if the cell passed as argument is locked and then create a conditional formatting rule using this function to change the background color for each locked cell. Obviously I cannot easily do this with an intermediate cell.

So again: Please Fix!

Jan

Lets assume A2 is the Top-Left most cell you want the conditional Formatting to be applied to, the just write in the formula "=A1<>""" or "=A1=""" 

Then apply this to all the ranges you need e.g. $A$1,$A$3,$B$2,$B$5:$C$9 (choose them with pressed CTRL)

And the formatting will work without custom VBA-Function.

BTW: I found that if the workbook is open and the bug is active, that the Error when saving also comes when I try to save a different open workbook! The other workbook is having (almost) no formatting and just a few simple charts and no VBA. Then I turn the bug off again in the conditional formatting workbook (as described previously) and then I can save the normal workbook. WTF?

I'm afraid this is an oversimplification. This will only format cells with no data. I want to highlight cells that cannot (or can, depending on my mood) be updated. Cells containing a label for example, contain a value, but should not be updated which is why I lock them.

Thank you sincerely.

Will give this a go and advise.

This *does not* occur *ever* in Excel 2010, *only* in Excel 2013.  So one way or another -- regardless whether this fix works -- it is an Excel 2013 issue that hopefully Microsoft will address going forward.

But again, thank you for taking the time to look into.

 - Mik

That is *exactly* why it fails. Rewrite your UDF to:

Function IsFormulaZ(c)

 IsFormulaZ = (Left(c.Formula, 1) = "=")

 End Function

For some odd reason, HasFormula does not work well in UDF's called from cells or Conditional formatting.