Exception in formula

Posted by: sean on 5 April 2018, 5:40 am EST

    • Post Options:
    • Link

    Posted 5 April 2018, 5:40 am 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 350GrapeCity.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)

  • Posted 9 April 2018, 4:37 pm EST - Updated 29 September 2022, 6:11 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

  • Posted 14 April 2018, 5:25 am 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), “–”)

  • Posted 15 April 2018, 7:37 pm 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

  • Posted 24 June 2018, 3:57 pm 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