Using External Variables with Text Box Control

The following example code shows how to create and use external variables with the standard textbox control.

In the example:

• There are two external variables : "x" and "y" and two textbox controls : "textBox1" and "textBox2"
• Cell B1 and textBox1 will provide the arguments for the formula. The arguments can be modified to display the updated result.
• The result will be displayed in textBox2.

// Creating and defining the external variable
public class TextBoxExternalVariable : ExternalVariable
{
    private TextBox _textBox;
    private bool _asInput;
    public TextBoxExternalVariable(TextBox textBox, bool asInput)
    {
        _textBox = textBox;
        if (asInput)
        {
            textBox.TextChanged += TextBox_TextChanged;
        }
    }
    private void TextBox_TextChanged(object sender, EventArgs e)
    {
        Dirty();
    }
   
    protected override bool OnDirtying()
    {
        return !_asInput;
    }
  
    protected override void OnDirtied()
    {
        Refresh();
    }
   
    protected override void EvaluateCore(IEvaluationContext context,
     IValue result)
    {
        string text = _textBox.Text;
        if (!string.IsNullOrEmpty(text) && double.TryParse(text,
             out double dblValue))
        {
            result.SetValue(dblValue);
        }
        else
        {
            result.SetValue(text);
        }
    }
   
    public void Refresh()
    {
        if (!_asInput)
        {
            _textBox.Text = this.Value.GetText();
        }
    }
} 
// Using the external variable with text box control
private void Form2_Load(object sender, EventArgs e)
{
 var workbook = fpSpread1.AsWorkbook();
 var activeSheet = workbook.ActiveSheet;
 activeSheet.Cells["A1"].Value = "Factor";
 activeSheet.Cells["B1"].Value = 2;
   
 // Adding Ext. Variable - x referring to textbox1
 workbook.Names.AddExternalVariable("x",
 new TextBoxExternalVariable(textBox1, true));
   
 // Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x"
 var extVariable2 = new TextBoxExternalVariable(textBox2, false);
 workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x");
 extVariable2.Refresh();
}

'Creating and defining the external variable
Public Class TextBoxExternalVariable Inherits ExternalVariable
Private _textBox As TextBox
Private _asInput As Boolean
Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean)
_textBox = textBox
If asInput Then
textBox.TextChanged += AddressOf TextBox_TextChanged
End If
End Sub
Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
Dirty()
End Sub
Protected Overrides Function OnDirtying() As Boolean
Return Not _asInput
End Function
Protected Overrides Sub OnDirtied()
Refresh()
End Sub
Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue)
Dim text As String = _textBox.Text
Dim dblValue As Double = Nothing
If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then
result.SetValue(dblValue)
Else
result.SetValue(text)
End If
End Sub
Public Sub Refresh()
If Not _asInput Then
_textBox.Text = Me.Value.GetText()
End If
End Sub
End Class    

'Using the external variable with text box control
Private Sub Form2_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim workbook = fpSpread1.AsWorkbook()
Dim activeSheet = workbook.ActiveSheet
activeSheet.Cells("A1").Value = "Factor"
activeSheet.Cells("B1").Value = 2
workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(textBox1, True))
Dim extVariable2 = New TextBoxExternalVariable(textBox2, False)
workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x")
extVariable2.Refresh()
End Sub