databased formulas and customnames to a databound cell

Posted by: bbrotherson on 8 September 2017, 12:35 pm EST

  • Posted 8 September 2017, 12:35 pm EST

    What is the best way to apply databased Formulas and CustomNames to a databound cell? Below is my code to do it unbound which works fine, but I want to take advantage of databinding for hierarchical and updating purposes.

    Dim dr As DataRow
    Dim dt As DataTable
    Dim ds As DataSet

    ds = MyDataSet
    dt = ds.Tables(0)

    fpsMaterials.AllowUserFormulas = True
    fpsMaterials.AllowUserZoom = True

    With fpsMaterials.Sheets(0)

    For Each dr In dt.Rows

    'set the value of the cell
    .Cells(i, 0).Text = dr("PartType")
    .Cells(i, 1).Text = dr(
    .Cells(i, 2).Text = dr(

    'Apply the formula
    .Cells(i, 2).Formula = "" & dr("Formula")
    strCellLoc = "$C$" & i + 1

    'Apply the CustomName
    Not IsDBNull(dr("CustomName")) Then
       .AddCustomName(dr("CustomName"), strCellLoc, 0, 0)
    End If

    .Cells(i, 3).Text = dr("Price")
    'Create the Ext. Formula
    .Cells(i, 4).Formula =
    "$C$" & i + 1 & " * $D$" & i + 1

    i += 1





    End With

  • Replied 8 September 2017, 12:35 pm EST


    There is not a way to bind the Formula property of a Spread control. You would need to treat these in an unbound manner like you are.

Need extra support?

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

Learn More

Forum Channels