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.

    <FONT size=2><FONT color=#0000ff size=2><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> dr </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> DataRow
    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> dt </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> DataTable
    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> ds </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> DataSet

    </FONT><FONT size=2>

    ds = MyDataSet
    </FONT><FONT size=2>dt = ds.Tables(0)


    fpsMaterials.AllowUserFormulas = </FONT><FONT color=#0000ff size=2>True
    </FONT><FONT size=2>fpsMaterials.AllowUserZoom = </FONT><FONT color=#0000ff size=2>True

    </FONT><FONT size=2>


    </FONT>With</FONT><FONT size=2> fpsMaterials.Sheets(0)

    </FONT></FONT><FONT color=#0000ff size=2></FONT>

    <FONT color=#0000ff size=2>For</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Each</FONT><FONT size=2> dr </FONT><FONT color=#0000ff size=2>In</FONT><FONT size=2> dt.Rows


    </FONT><FONT color=#008000 size=2>'set the value of the cell
    </FONT><FONT size=2>.Cells(i, 0).Text = dr(</FONT><FONT color=#a31515 size=2>"PartType"</FONT><FONT size=2>)
    .Cells(i, 1).Text = dr(</FONT><FONT color=#a31515 size=2>"PartName"</FONT><FONT size=2>)
    .Cells(i, 2).Text = dr(</FONT><FONT color=#a31515 size=2>"Qnty"</FONT><FONT size=2>)


    </FONT><FONT color=#008000 size=2>'Apply the formula
    </FONT><FONT size=2>.Cells(i, 2).Formula = </FONT><FONT color=#a31515 size=2>""</FONT><FONT size=2> & dr(</FONT><FONT color=#a31515 size=2>"Formula"</FONT><FONT size=2>)
    </FONT><FONT size=2>strCellLoc = </FONT><FONT color=#a31515 size=2>"$C$"</FONT><FONT size=2> & i + 1</FONT>


    <FONT color=#0000ff size=2><FONT color=#008000>'Apply the CustomName</FONT>
    If</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Not</FONT><FONT size=2> IsDBNull(dr(</FONT><FONT color=#a31515 size=2>"CustomName"</FONT><FONT size=2>)) </FONT><FONT color=#0000ff size=2>Then
      </FONT><FONT size=2>   .AddCustomName(dr(</FONT><FONT color=#a31515 size=2>"CustomName"</FONT><FONT size=2>), strCellLoc, 0, 0)
    </FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>If

    </FONT><FONT size=2>

    .Cells(i, 3).Text = dr(</FONT><FONT color=#a31515 size=2>"Price"</FONT><FONT size=2>)
    <FONT color=#008000>'Create the Ext. Formula</FONT>
    .Cells(i, 4).Formula = </FONT><FONT color=#a31515 size=2>"$C$"</FONT><FONT size=2> & i + 1 & </FONT><FONT color=#a31515 size=2>" * $D$"</FONT><FONT size=2> & i + 1
    </FONT><FONT size=2>
    i += 1


    </FONT><FONT color=#0000ff size=2>Next</FONT>


    <FONT color=#0000ff size=2></FONT> 


    <FONT color=#000000 size=2>Thanks,</FONT>


    <FONT color=#000000 size=2>Brad</FONT>


    <FONT color=#0000ff size=2>End With

    </FONT>
  • Replied 8 September 2017, 12:35 pm EST

    Brad,


    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