Spread Windows Forms 18 Product Documentation

Creating and Using External Variable

Spread for WinForms provides support for creating and using external variables in spreadsheets.

An external variable refers to a logical cell defined by a symbol. The name of an external variable is case-sensitive. External variables don't possess a cell context and are evaluated distinctly than a normal cell.

If a reference in the formula for the external variable is missing a reference to the worksheet, then that reference will refer to the active sheet.
Formulas for external variables are always evaluated using absolute references. If the formula for the external reference uses relative references, then those references will be converted to absolute references when evaluating the formula.
The external variable formula can result in a range reference when it is evaluated; in this case, the external variable can be used in an array formula. If such a formula is evaluated for a single cell, which requires a primitive value result, then the value in the top-left cell in the range is used as the context cell, and that cell's value will be returned as the evaluated result.
The external variable can specify a class instance which inherits from ExternalVariable, which enables the application to implement custom logic to bind external objects with the calculation engine. For a detailed example showing how to inherit from ExternalVariable, refer to Using External Variables with TextBox Control.

The AddExternalVariable method of the INames interface can be used to add an external variable to the workbook. While using this method, users need to provide the name of the external variable and the formula or the value of the variable.

Using Code

Define the external variable using the AddExternalVariable method for the workbook.

Example

The following example code creates two external variables "x" and "y" and uses them within the formulas defined in a particular worksheet of the workbook.

// Add a new sheet to your workbook.
FarPoint.Win.Spread.SheetView newsheet = new FarPoint.Win.Spread.SheetView();
fpSpread1.Sheets.Add(newsheet);
fpSpread1.AllowUserFormulas = true;

// Set the values of A1 cell and A2 cell of the active sheet.
fpSpread1.ActiveSheet.Cells[0, 0].Value = "Hello";
fpSpread1.ActiveSheet.Cells[1, 0].Value = "World!";

// Add external variables to the workbook that refer to the value of the cells.
fpSpread1.AsWorkbook().Names.AddExternalVariable("x", "Sheet1!A1");
fpSpread1.AsWorkbook().Names.AddExternalVariable("y", "Sheet1!A2");

// Use the external variables in different cell.
fpSpread1.AsWorkbook().Worksheets[1].Cells["B1"].Formula = "\"The concat name is \" & x & y";

Add a new sheet to your workbook.
Dim newsheet As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView()
fpSpread1.Sheets.Add(newsheet)
fpSpread1.AllowUserFormulas = true

' Set the values of A1 cell and A2 cell of the active sheet.
fpSpread1.ActiveSheet.Cells(0, 0).Value = "Hello"
fpSpread1.ActiveSheet.Cells(1, 0).Value = "World!"

' Add external variables to the workbook that refer to the value of the cells.
fpSpread1.AsWorkbook().Names.AddExternalVariable("x", "Sheet1!A1")
fpSpread1.AsWorkbook().Names.AddExternalVariable("y", "Sheet1!A2")

' Use the external variables in different cell.
fpSpread1.AsWorkbook().Worksheets(1).Cells("B1").Formula = """The concat name is "" & x & y"

Creating and Using External Variable

Using Code

Example

See Also