How can I prevent the green warning triangle from displaying in a formula-bound cell (C# Excel Interop)?

Question

I've got this formula that generates the correct data:

var avgWeeklyDeliveriesCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow, AVG_WEEKLY_DELIVERIES_COLUMN];
avgWeeklyDeliveriesCell.Value2 = string.Format("=ROUND(AVERAGE(C{0}:I{0}), 2)", curDelPerfRow);

The problem is that it overthinks/micro-manages matters, and wonders if an adjacent cell (the one to the left, I presume, "Total Orders") should be included in the formula.

Specifically, if I click on the green triangle, a "!" glyph pops out; clicking that has a msg, "Formula Omits Adjacent Cells"

And so, it tags all the formula-bound cells with a green triangle.

Here is how it looks:

As you can see, the column with the formula always sports the little green triangle in the NW corner. And the formula is correct (averaging the values from "Sun Orders" through "Sat Orders" inclusive).

What can I do to tell Excel to "cool it" and prevent the little green triangles from displaying?

UPDATE

Both answers so far seem reasonable, but neither work.

I must admit, though, that my main Excel object is a little different than what Varocarbas has. My code is:

using Microsoft.Office.Interop.Excel;
using Application = System.Windows.Forms.Application;
. . .
private ApplicationClass _xlApp;
. . .
_xlApp = new ApplicationClass { UserControl = true };
_xlApp.ErrorCheckingOptions.BackgroundChecking = false;

IOW, I'm using ApplicationClass instead of Excel.Application.

UPDATE 2

I changed my code to this (no longer using ApplicationClass):

using Excel = Microsoft.Office.Interop.Excel;
. . .
//private Excel.ApplicationClass _xlApp;
private Excel.Application _xlApp = new Excel.Application();
. . .

//_xlApp = new Excel.ApplicationClass { UserControl = true };
_xlApp = new Excel.Application();
_xlApp.ErrorCheckingOptions.BackgroundChecking = false;

...but I still get the little green triangles. Worse yet, I am starting to hear Bobby Goldsboro singing "God didn't make little green triangles" in my mind's ear.

UPDATE 3

I reckon the problem is probably not "closing properly the Excel processes." In Task Manager, I see quite a few "EXCEL.EXE *32" instances. I must be failing to 86 the Excel processes; here is my code that I thought should do that:

foreach (DataRowView drv in selectedUnits)
{
    Application.DoEvents();
    _xlApp = new Excel.Application();
    _xlApp.ErrorCheckingOptions.BackgroundChecking = false;

    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    _xlApp.ActiveWindow.DisplayGridlines = false;
    _xlApp.SheetsInNewWorkbook = 1; // prevent the empty "sheet 2" etc.
    _xlSheets = _xlBook.Worksheets;

    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
    if (_xlSheet != null)
    {
        _xlSheet.Name = ProduceUsageByMonthSheetName;   
        . . .
        var filename = Properties.Settings.Default.OutputDirectory + shortUnitName + ".xlsx";
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }

        _xlBook.SaveAs(Properties.Settings.Default.OutputDirectory + shortUnitName + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Marshal.ReleaseComObject(_xlSheet);
        _xlBook.Close(false, null, null);
        Marshal.ReleaseComObject(_xlBook);
        _xlApp.DisplayAlerts = false;
        _xlApp.Quit();
        _xlSheets = null;
    }
    _xlSheet = null;
    _xlBook = null;
    _xlApp = null;
    OnChanged(EventArgs.Empty);
} // foreach (DataRowView drv in selectedUnits)

UPDATE 4

I'm not at all sure this is the very best way to do it, but the order of releasing makes sense to me (sheet, then sheets, then book, then app), and empirically speaking, I am not only no longer seeing the little green meanies, but I have no more extraneous/superfluous instances of Excel lurking about the innards of the devbox.

Here is what I changed the above release and null mess to:

    . . .
    }
    Marshal.ReleaseComObject(_xlSheet);

    Marshal.ReleaseComObject(_xlSheets);

    _xlBook.Close(false, null, null);
    Marshal.ReleaseComObject(_xlBook);

    _xlApp.DisplayAlerts = false;
    _xlApp.Quit();
} // foreach (DataRowView drv in selectedUnits)
Marshal.ReleaseComObject(_xlApp);

I may not need all of it (such as _xlBook.Close() and _xlApp.Quit), but at least it's working this way.

I have replaced Excel Interop with EPPlus (and Aspose when I need a PivotTable). I am getting the "little green meanies" again and in this case, the releasing and order thereof do not apply, as this is not done explicitly with these libraries - they take care of object lifetime themselves. — B. Clay Shannon-B. Crow Raven, Dec 26 '16 at 18:11

varocarbas · Accepted Answer · 2015-12-02T18:59:49.750

2

This green triangle indicates that the formula in the given cell contains an error according to certain rules (more information about this).

You can enable/disable this behaviour at the Excel application level, by changing the value of the property ErrorCheckingOptions.BackgroundChecking. Sample code:

Excel.Application excelApp = new Excel.Application();
excelApp.ErrorCheckingOptions.BackgroundChecking = false;

edited Dec 02 '15 at 18:59

answered Dec 02 '15 at 18:53

varocarbas

12,354
4
26
37

That also makes no diff - I still get the little green triangles. Please see my Update, though. – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 19:02
Did you note my Update? – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 19:07
@B.ClayShannon Yes. I haven't ever seen such a thing (and I have created quite a few Excel Interop apps). Cannot you plainly rely on Excel.Application? – varocarbas Dec 02 '15 at 19:09
@B.ClayShannon "This class supports the .NET Framework infrastructure and is not intended to be used directly from your code." https://msdn.microsoft.com/es-es/library/microsoft.office.interop.excel.applicationclass(v=office.11).aspx – varocarbas Dec 02 '15 at 19:12
1

@B.ClayShannon Take also a look at: http://stackoverflow.com/questions/14016264/microsoft-office-interop-excel-applicationclass-has-no-constructor-defined – varocarbas Dec 02 '15 at 19:12
I added a second Update. – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 19:37
@B.ClayShannon This is certainly very weird (and shouldn't happen at all). If you go to Options -> Formulae, you should see how the "Enable background error checking" is being un/checked by using this approach. There is something wrong at your end (you might not be saving the information, you might not be closing properly the Excel processes, you might be looking at the wrong files, etc.). You should make some basic checks to dismiss sources of errors, like checking/unchecking this option manually or creating a small app consisting just in an Excel.Application and see if it works. – varocarbas Dec 02 '15 at 19:45
No, "Enable background error checking" is still checked in Excel Options.Formulas.Error Checking. – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 19:46
@B.ClayShannon Then your application is not communication properly with Excel (and the green triangles should disappear as soon as this will work). Just create a new project including the minimum information (Excel reference + Excel app + ErrorCheckingOptions.BackgroundChecking = false + workbook + save) and see if it works. Also better kill all the running Excel processes to make sure. – varocarbas Dec 02 '15 at 19:48
I reckon the problem is probably not "closing properly the Excel processes." In Task Manager, I see quite a few "EXCEL.EXE *32" instances. I'll add a third Update with my cleanup code. – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 19:51
1

@B.ClayShannon I see your last update and, as I assumed, your code is not releasing the Excel objects properly, what provokes lots of problems (no, it is not enough with setting all the variables to null; this code will continue giving problems until this issue will be fixed. Properly releasing the Excel objects is one of the basic actions when using Excel Interop. Do some research on this front and you will easily solve this). So I understand that now my suggestion works fine in your computer too? – varocarbas Dec 02 '15 at 20:54
Yes, after 86ing about 40,000 Excel instances, it finally "took" – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 21:13
1

@B.ClayShannon Lesson learned for the future: when dealing with MS Office make always sure that everything is properly closed/released (Excel is perhaps the most problematic one, but the other programs also need some attention on this front). – varocarbas Dec 02 '15 at 21:17
1

Yep, thanks; I'll put my release code in a final Update for "full disclosure" – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 21:31
I have abandoned Excel Interop for EPPlus, and now I wish there was a "ErrorCheckingOperations" available from there... – B. Clay Shannon-B. Crow Raven Sep 12 '16 at 18:43

score 1 · Answer 2 · answered Dec 02 '15 at 18:52

1

Try adding the following line

avgWeeklyDeliveriesCell.Errors[XlErrorChecks.xlInconsistentFormula].Ignore = true;

answered Dec 02 '15 at 18:52

Ivan Stoev

195,425
15
312
343

No diff, although it looks perfectly logical. – B. Clay Shannon-B. Crow Raven Dec 02 '15 at 18:59

score 1 · Answer 3 · answered Apr 26 '16 at 18:02

1

My answer is pretty close to Ivan's response. Other Error Types for cells can be found here on msdn.

avgWeeklyDeliveriesCell.Errors[XlErrorChecks.xlOmittedCells].Ignore = true;

answered Apr 26 '16 at 18:02

drew2apps

11
2

How can I prevent the green warning triangle from displaying in a formula-bound cell (C# Excel Interop)?

UPDATE

UPDATE 2

UPDATE 3

UPDATE 4

3 Answers3

Linked