Formula and DataBinding

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

  • Posted 8 September 2017, 1:46 pm EST

    <P>I  use databinding and add Unbound Row which will display totals using Formula:</P>
    <P><FONT color=#000000>With FPSpread1.ActiveSheet</FONT></P>
    <P>'Set up data source    </P>
    <P>.DataSource  =... </P>
    <P><FONT color=#000000>'Setup columns </FONT></P>
    <P><FONT size=2> </P></FONT><FONT color=#000000><FONT size=2><FONT size=2>
    <P></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'Add unbound column</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>'Add totals row</P></FONT></FONT><FONT size=2>
    <P>.AddUnboundRows(.RowCount, 1)</P>
    <P>.Cells(.RowCount - 1, intTotalsGridColumn).Text = </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Totals:"</P>
    <P></FONT></FONT>.Cells(.RowCount - 1, intSumColumn).Formula = </FONT><FONT size=2><FONT size=2>"SUM(R1C"</FONT></FONT><FONT size=2> & (intSumColumn + 1).ToString & </FONT><FONT size=2><FONT size=2>":R"</FONT></FONT><FONT size=2> & (.RowCount - 1).ToString & </FONT><FONT size=2><FONT size=2>"C"</FONT></FONT><FONT size=2> & (intSumColumn + 1).ToString & </FONT><FONT size=2><FONT size=2>")"</FONT></FONT></FONT></P>
    <P>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.</P>
    <P>Is there any way to set up a formula using RC reference when Data source doesn't have any rows?</P>
    <P>Thank you </P>
    <P> </P>
    <P><FONT color=#a31515 size=2><FONT color=#a31515 size=2> </P></FONT></FONT>
  • Replied 8 September 2017, 1:46 pm EST

    <P>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.</P>
  • Replied 8 September 2017, 1:46 pm EST

    <P>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 </P>
    <P>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?</P>
    <P>Thank you for your response</P>
  • Replied 8 September 2017, 1:46 pm EST

    <P>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.</P>
  • Replied 8 September 2017, 1:46 pm EST

    <P>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).</P>
    <P>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.</P>
Need extra support?

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

Learn More

Forum Channels