Formula always returns zero

Posted by: mikedempsey on 8 September 2017, 12:41 pm EST

  • Posted 8 September 2017, 12:41 pm EST

    I am trying to display the sum of any numeric cells within the block of cells selected by the user. (Similar to Excel)

    This works using my old app with the COM version of Spread, but it always displays '0.00' in the .Net 3.0 version.

    There are no failures in the code and the formula appears to be correct when I check it in debug mode - 'SUM(R0C1:R15C1)'  for example.  (I did tell it to use R1C1 notation rather than A1 notation, so that's not it.)

    I have attached the code I added to the SelectionChanged event. Any pointers as to what might be wrong? 

    <SPAN class=kwd>Private Sub</SPAN> Answer_SelectionChanged(<SPAN class=kwd>ByVal</SPAN> sender <SPAN class=kwd>As Object</SPAN>, <SPAN class=kwd>ByVal</SPAN> e <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.SelectionChangedEventArgs) <SPAN class=kwd>Handles Me</SPAN>.SelectionChanged
    <SPAN class=cmt>'TODO: Display the SUM if a block is selected
    <SPAN class=kwd>If</SPAN> e.Range.ColumnCount <> 1 <SPAN class=kwd>Or</SPAN> e.Range.RowCount <> 1 <SPAN class=kwd>Then
    Dim</SPAN> r <SPAN class=kwd>As Integer</SPAN> = Sheets(0).RowCount
    <SPAN class=kwd>Dim</SPAN> cr <SPAN class=kwd>As</SPAN> Model.CellRange = e.Range
    <SPAN class=cmt>'Row and rowCount etc can be -1 for whole Row/column
    <SPAN class=kwd>If</SPAN> cr.RowCount = -1 <SPAN class=kwd>Then</SPAN> _
    cr = <SPAN class=kwd>New</SPAN> Model.CellRange(0, cr.Column, r, cr.ColumnCount)
    <SPAN class=kwd>If</SPAN> cr.ColumnCount = -1 <SPAN class=kwd>Then</SPAN> _
    cr = <SPAN class=kwd>New</SPAN> Model.CellRange(cr.Row, 0, cr.RowCount, Sheets(0).ColumnCount)
    Sheets(0).AddRows(r, 1)
    Sheets(0).Cells(r, 0).Locked = <SPAN class=kwd>False</SPAN>
    Sheets(0).Cells(r, 0).CellType = <SPAN class=kwd>New</SPAN> CellType.NumberCellType()
    Sheets(0).Cells(r, 0).Formula = <SPAN class=st>"sum(R"</SPAN> & cr.Row + 1 & <SPAN class=st>"C"</SPAN> & cr.Column + 1 & _
    <SPAN class=st>":R"</SPAN> & cr.Row + cr.RowCount & <SPAN class=st>"C"</SPAN> & cr.Column + cr.ColumnCount & <SPAN class=st>")"</SPAN>
    MainFrm.SetStatus(Sheets(0).Cells(r, 0).Text) <SPAN class=cmt>'<<< Always zero!
    Sheets(0).RemoveRows(r, 1)
    <SPAN class=kwd>End If
    End Sub</SPAN>
  • Replied 8 September 2017, 12:41 pm EST


    A couple of issues I see. The R1C1 format is 1 based index where the rows and columns in the Spread are 0 based. Since you are adding a row before the cells being selected, you need to add 2 to the row properties of the selection instead of 1. The other thing is to make sure all cells in the selection being summed are a number (5) and not a string representation of a number("5").

  • Replied 8 September 2017, 12:41 pm EST

    The row I am adding is at the end of the spreadsheet so adding 1 (to allow for the 1 based formulas) should be correct.

    Maybe the problem is due to numeric 'strings'. I defined the entire sheet as numeric but loaded it from a file using the LoadFile [or whatever it is called] method. If that method resets the cell types it might explain it. (I know the LoadExcel method resets everything but i didnt thing the loadFile did.)

    I am also assuming that if there are non numeric cells in the range they will simply be treated as zeros (as in the COM version) - they will not cause it to return zero. [That was not the case in my tests however - all cells containd 'numbers'.] 

  • Replied 8 September 2017, 12:41 pm EST


    If you are using the LoadTextFile method, only the data is removed from the Spread before loading the text. Then, the text file is read in as string values into the cells. If you set the dataonly parameter of this method to false and there is a CellType assigned to the cell, then the Parse method for that celltype is called to parse the data for the DataModel. Otherwise the data will be put in the DataModel as strings.

    If you have a NumberCellType, then the string numbers being read in from the text file would be parsed into numbers.

    If you are using the Open method to load files into the Spread, then the CellType information will be resetted and read in from the file you are loading. The data is loaded in the same format as it is stored in the XML file. If you have a number stored as a string, then it will look like a number in the Spread, but act like a string in the DataModel and mess up your formulas.

  • Replied 8 September 2017, 12:41 pm EST

    I assume you mean to set the 'unformatted' parameter to False. (There is no 'dataonly' parameter.)

    I had assumed that 'unformatted' simply prevented it from applying formats - such as thousand separators, $ signs etc - so I specified True, since I did not require those.  I did not realize that it meant it would treat the data as strings. (The help is not very useful on this (many) topics.)

    After I set the parameter to False the formula works fine, thanks.


Need extra support?

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

Learn More

Forum Channels