Formula and DataBinding

Posted by: echernyak on 8 September 2017, 1:46 pm EST

  • Posted 8 September 2017, 1:46 pm EST

    I  use databinding and add Unbound Row which will display totals using Formula:

    With FPSpread1.ActiveSheet

    'Set up data source   

    .DataSource  =... 

    'Setup columns 


    'Add unbound column

    'Add totals row

    .AddUnboundRows(.RowCount, 1)

    .Cells(.RowCount - 1, intTotalsGridColumn).Text = "Totals:"

    .Cells(.RowCount - 1, intSumColumn).Formula = "SUM(R1C" & (intSumColumn + 1).ToString & ":R" & (.RowCount - 1).ToString & "C" & (intSumColumn + 1).ToString & ")"

    If at the time DataSource doesn't have any records Spread will contain only one Summary (unbound) row and formula above wouldn't work. I can bypass setting formula at this time and try to set it in event when row is being added.

    Is there any way to set up a formula using RC reference when Data source doesn't have any rows?

    Thank you 



  • Replied 8 September 2017, 1:46 pm EST


    Why do you want the formula when there is no records to add up? There is not going to be a way to put a formula like this into the first row.

  • Replied 8 September 2017, 1:46 pm EST

    If it's data entry application and initially datasource is empty, program needs to add formula when adding first row and remove formula when user deletes last row

    Do you think it's the best way to handle it? Is it better to use Spread events or ADO events for underlined datasource tables?

    Thank you for your response

  • Replied 8 September 2017, 1:46 pm EST


    If the Spread control is the only place to add delete records, then using Spread events would work. Otherwise you would need to use the ADO events for the bound DataSource.

  • Replied 8 September 2017, 1:46 pm EST


    I would like to expand on Scott's answers.  Spread uses Excel's syntax for entering formulas.  Excel's syntax does not provide any means for specifying a range with zero rows (or zero columns).  In Excel's syntax, you specify a cell range with a start cell and an end cell.  If the start cell and end cell are in the same row (or same column) then the cell range contains one row (or one column).  If the start cell and end cell are in different rows (or different columns) then the cell range contains more than one row (or more that one column).  Thus, a cell range will always contain one or more rows (and one or more columns).

    In your example code, suppose intSumColumn = 4.  When there are no bound rows and one unbound row, your code would have attempted to assign the formula "SUM(R1C5:R0C5)".  However, R0 is not a valid row name in the R1C1 notation.  Thus, the formula would be rejected as an invalid formula.

Need extra support?

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

Learn More

Forum Channels