Excel saves file, but throws error on opening it: "We found a prob-lem with some content in …"

Question

I was able to save a workbook, but then when I reopened it, Excel complained, "We found a problem with some content in 'filename'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

"Yes" (i.e., recover) causes offending Drawings/Shapes/Charts to be deleted.

The following describes the cause of my problem and its cure.

Rocky Scott Rocky Scott · Accepted Answer · 2016-06-15T16:30:33

I had a user-defined worksheet function (UDF) that returned an array of values that was displayed in a chart.

The UDF was setting some of these values to the impossible value of 2.69653970229349E+308 (apparently a dll was returning 1.#QNAN which Excel turned into 2.69653970229349E+308).

Amazingly, Excel allowed this value to be saved in the cell, and the workbook to be saved. However, when opening the workbook, the error was received, and the offending chart was deleted during the "recovery".

The cure was to ensure only realistic values are displayed in a chart.

Here is code redacted from the rather large VBA project.

'Global array, Values returned by the UDF
Public MainReturnValues(1 To 57, 1 To 2) As Variant

Public Function MainWorksheetFunction( _
      i_rRangeWithInputData As Range _
      ) As Variant

   '[... Process input data ...]

   'Load global array MainReturnValues(,), i.e., values returned by the UDF
   LoadMainWorksheetFunctionReturnValues

   'Now load UDF return values
   MainWorksheetFunction = MainReturnValues

End Function

'Load global array MainReturnValues(,), i.e., values returned by the UDF
Private Function LoadMainWorksheetFunctionReturnValues( _
      ) As Boolean

   Dim l_vErrNull As Variant
   Dim l_dChartXs(1 To 57) As Double, _
       l_dChartYs(1 To 57) As Double
   Dim c As Long, _
       r As Long

   '~~~~~~~ First fill area with xlErrNull to Prevent Log Chart Graphing 0 Errors
   '~~~~~~~ Excel will "pick up the pen" when cells contain CVErr(xlErrNull)
   l_vErrNull = CVErr(xlErrNull)
   For r = LBound(MainReturnValues, 1) To UBound(MainReturnValues, 1)
      For c = LBound(MainReturnValues, 2) To UBound(MainReturnValues, 2)
         MainReturnValues(r, c) = l_vErrNull
      Next c
   Next r
   '~~~~~~~ End First fill area with xlErrNull ~~~~~~~

   '[... Load values into l_dChartXs() and l_dChartYs() using C# dll ...]

   For r = 1 To 57

      'Don't try to plot ridiculous values. I suspect this causes this error:
      '"We found a problem with some content in 'aaa.xlsb'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."
      On Error Resume Next 'Catch 1.#QNAN
      If ((l_dChartXs(r) > 1E+308) Or (l_dChartYs(r) > 1E+308)) Then

      Else 'Value is not ridiculous
         On Error GoTo 0

         'Xs in first column
         MainReturnValues(r, 1) = l_dChartXs(r)

         'Ys in second column
         MainReturnValues(r, 2) = l_dChartYs(r)
      End If
   Next r

   LoadMainWorksheetFunctionReturnValues = True
Exit Function

I hope this helps someone.

Excel saves file, but throws error on opening it: "We found a prob-lem with some content in …"

2 Answers