Exception in formula

Posted by: sean on 5 April 2018, 2:40 pm EST

  • Posted 5 April 2018, 2:40 pm EST

    I get an exception when this line of code tries to put a formula in a cell

    ws[row, colIndex].Formula = "IF(R[-1]C <> \"\", R[-1]C - SUM(R[2]C:R[100]C), \"--\")";

    I have tried typing the same formula directly in the spreadsheet and it also does not work with relative references, but it does work with A1 style references. As far as I can tell these relative references are correct as specified in the help file.

    Here is the stack trace:

    at BuildWorksheet() in C:\Projects\Test\Test\ViewModels\WSViewModel.cs:line 350</StackTrace><ExceptionString>GrapeCity.CalcEngine.CalcParseException: Invalid cell reference or name.
    Error offset:3
    at GrapeCity.CalcEngine.CalcParser.b(CalcParserContext A_0, String A_1, Int32 A_2)
    at GrapeCity.CalcEngine.CalcParser.d(CalcParserContext A_0, b A_1)
    at GrapeCity.CalcEngine.CalcParser.a(CalcParserContext A_0, List`1 A_1)
    at GrapeCity.CalcEngine.CalcParser.b(CalcParserContext A_0, List`1 A_1)
    at GrapeCity.CalcEngine.CalcParser.c(CalcParserContext A_0, b A_1)
    at GrapeCity.CalcEngine.CalcParser.d(CalcParserContext A_0, b A_1)
    at GrapeCity.CalcEngine.CalcParser.a(CalcParserContext A_0, List`1 A_1)
    at GrapeCity.CalcEngine.CalcParser.b(CalcParserContext A_0, List`1 A_1)
    at GrapeCity.CalcEngine.CalcParser.Parse(String text, CalcParserContext context)
    at GrapeCity.CalcEngine.CalcCalculationManager.a(CalcLocalIdentity A_0, String A_1, Boolean A_2)
    at GrapeCity.CalcEngine.CalcCalculationManager.SetFormula(CalcCellIdentity id, String formula, Boolean isArrayFormula)
    at GrapeCity.Windows.SpreadSheet.Data.c.c(CalcCalculationManager A_0, CalcLocalIdentity A_1, String A_2, Boolean A_3)
    at GrapeCity.Windows.SpreadSheet.Data.Worksheet.l(Int32 A_0, Int32 A_1, Int32 A_2, Int32 A_3, SheetArea A_4, String A_5, Boolean A_6, Boolean A_7)
    at GrapeCity.Windows.SpreadSheet.Data.Cell.set_Formula(String value)
  • Replied 10 April 2018, 1:37 am EST

    Hello,

    It seems that formula is incorrect. I tried to use this formula in Spread Designer and MS Excel it does give error. Please refer to the screenshot.

    Thanks,
    Deepak Sharma
  • Replied 14 April 2018, 2:25 pm EST

    It works in Excel as expected if I take out the \ characters (it was C# code in my message, not a direct formula), and enable R1C1 reference style.

    =IF(R[-1]C <> "", R[-1]C - SUM(R[2]C:R[100]C), "--")
  • Replied 16 April 2018, 4:37 am EST

    Hi Sean,

    The given formula works absolutely fine with Spread WPF/Silveerlight Designer. It also works if I enter the formula in cell at run time.
    However setting the formula in code gives an exception:
    "System.ArgumentOutOfRangeException: 'Length cannot be less than zero.'"

    I have submitted it as a bug for the development team to test. I will let you know as soon as I get an update on this. The tracking id for this issue is : 257134

    Thanks,
    Deepak Sharma
  • Marked as Answer

    Replied 25 June 2018, 12:57 am EST

    Hello,

    Developers found that the formula was not set correctly. The formula should be written like
    gcSpreadSheet1.ActiveSheet.Cells[5, 5].Formula = "=IF(R[-1]C <> \"\", R[-1]C - SUM(R[2]C:R[100]C), \"--\")";

    Thanks,
    Deepak Sharma
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels