Writing functions in Excel from C#

Question

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

possible duplicate: stackoverflow.com/questions/16400302/… — ASh
– ASh, Commented May 29, 2017 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
– Roofy, Commented May 29, 2017 at 17:42
is it excel.interop? try Value property (set_Value is a setter) — ASh
– ASh, Commented May 29, 2017 at 17:44

smartobelix · Accepted Answer · 2017-05-30 06:57:55Z

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

answered May 30, 2017 at 6:57

smartobelix

7485 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roofy Over a year ago

You are genious! Thank a lot it works. The problem was with semicolon ";" => "," :)

Collectives™ on Stack Overflow

Writing functions in Excel from C#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related