Setting a formular to cell result adds additional rows to the activeSheet

Posted by: hpmx on 8 September 2017, 1:44 pm EST

  • Posted 8 September 2017, 1:44 pm EST


    testing then rowcount resulst in:


    after executing the following statement:

    Me.fpsTargetValues.ActiveSheet.Cells(7, 3).Formula="SUM($D$1:$D$6)"

    testing the rowcount results in:


    and i get an "index out of bounds" message!


    Any hint why?

    Regards hpm



  • Replied 8 September 2017, 1:44 pm EST


    Formula assignment (and recalculation) should not affect the row count.  I can not reproduce the issue with the provided information.  My guess is that you have something else occuring in the application that is causing the adding of the additional rows.  Do you have any event handlers that could be adding the rows (e.g. Changed event on sheet data model is raised as cell values are updated during a recalculation)?  Is you sheet bound to a data source that is dynmically changing dimensions?  Could you provide us with an application (or code snippet) that reproduces the problem?

  • Replied 8 September 2017, 1:44 pm EST


    I looked once more on our source code and found the following:

    (actual row size is 6)


    with Me.fpsTargetValues.ActiveSheet                       

    .Rows.Add(idx, 2)
     .Rows(idx).BackColor = Color.Black
      .Rows(idx).Height = 2
     .Cells(idx + 1, 0).Value = "SUMME"
     .Cells(idx + 1, 3).Formula = "SUM($D$1:$D$" & idx & ")"
     intPoints = CInt(.Cells(idx + 1, 3).Value)
     .Cells(idx + 1, 5).Formula = "SUM($F$1:$F$" & idx & ")"
     intPointsPROG = CInt(.Cells(idx + 1, 5).Value)

    /end code

    Is it allowed to add rows to a sheet, which is bound to a datasource  by saying " .rows.add(...) or have we to do this in the underlying datasource itsself?

     Furthermor is allowed to add formulars to sheet bounded to a datasource?

    Thanks in advance 









  • Replied 8 September 2017, 1:44 pm EST


    It is legal to call Rows.Add on a bound Spread. However, this will add the row and it's data to the bound DataSource, which may not be the result you are looking for. In this case, you need to use the AddUnboundRows method to add rows to the Spread that are not bound to the DataSource.

Need extra support?

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

Learn More

Forum Channels