0

I have a problem with writing functions from C# into Excel file.

private void AddDemandFunctions()
{
    Func<string, string, string> function = (range, shift) =>
    {
        var builder = new StringBuilder();
        builder.Append("=LICZ.JEŻELI(");
        builder.Append(range);
        builder.Append("; \"");
        builder.Append(shift);
        builder.Append("\")");
        return builder.ToString();
    };

    int startRow = 5;
    int endRow = 20;
    string [] columns = { "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM" };

    foreach (var column in columns)
    {
        string range = column + startRow + ":" + column + endRow;
        worksheet.Range[column + 21].Formula = function(range, "D"); // HERE I GET AN EXCEPTION RIGHT IN MY FACE :(
        worksheet.Range[column + 22].Formula = function(range, "E");
        worksheet.Range[column + 23].Formula = function(range, "L");
        worksheet.Range[column + 24].Formula = function(range, "N");
    }
}

The exception is:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Wyjątek od HRESULT: 0x800A03EC.

My Excel(2010) is Polish language version. When I tried to delete "=" it works, but it writes only text. Then, if I add "=" inside excel file everything is ok. There is no mistake inside the function string.

I also tried with non-polish function: COUNTIF and I have the same exception...

Any ideas?

Greetings from PL :)

J-Alex
  • 6,881
  • 10
  • 46
  • 64
Roofy
  • 89
  • 2
  • 9
  • possible duplicate: https://stackoverflow.com/questions/16400302/excel-worksheet-cellsrow-col-formula-vs-range-set-valuemissing-value-a – ASh May 29 '17 at 17:17
  • I think that I am using different library because I don`t have metod set_Value on Range object like in your topic... – Roofy May 29 '17 at 17:42
  • is it excel.interop? try `Value` property (`set_Value` is a setter) – ASh May 29 '17 at 17:44
  • yes it is. Value also doesn`t work - same exception :( – Roofy May 29 '17 at 18:30

1 Answers1

1

You have to check what is really inside cell when you enter formula manually. Excel shows "=COUNTIF(E5:E20; "D")" in formula bar, but if you read real content you will see there "=COUNTIF(E5:E20, "D")" <- please notice comma instead of semicolon in formula.

How to do it? Write formula into cell manually, open VBA editor (Alt+F11 or Ctrl+G) and type in Immediate window i.e. ? Worksheet.range("G1").Formula - depends where you entered formula of course.

If you will format your formula according to what is displayed excel will not throw anything in your face ;-)

I suppose there is also comma->semicolon problem.

EDIT: Sorry worksheet was my local variable. You have to use syntax:

? ActiveWorkbook.ActiveSheet.range("G1").Formula

smartobelix
  • 748
  • 5
  • 16