External Variables

Spread for WPF allows you to create and use external variables within worksheets. These external variables are defined by symbols and have case-sensitive names. Unlike regular cells, external variables, which represent logical cells in a worksheet, do not have a cell context and are evaluated differently.

When working with external variables, keep the following considerations in mind:

• Formulas that reference external variables are always evaluated using absolute references.

• If a relative reference is used in a formula with an external variable, it is automatically converted to an absolute reference during evaluation.

• A formula that involves an external variable may result in a range reference. In such cases, the external variable can be used within an array formula.

• When a formula referencing an external variable is evaluated for a single cell that requires a primitive value, the top-left cell of the range is used as the context cell, and its value is returned as the evaluation result.

• If a formula referencing an external variable does not specify a worksheet reference, it will refer to the active sheet by default.

To add an external variable to a workbook, use the AddExternalVariable method of the INames interface. When using this method, you must specify the name of the external variable along with its formula or value. You can also implement custom logic to create an instance of the ExternalVariable class and bind an external object.

The following GIF illustrates how to use external variables within a worksheet by defining detailed information about the sales report on a separate sheet of the workbook.

Refer to the following example code to create and use external variable within the worksheet.

C#

// Add another sheet in the workbook.
GrapeCity.Spreadsheet.IWorksheet newWorksheet = spreadSheet1.Workbook.Worksheets.Add();
// Add an external variable to your workbook that references the cell value.
spreadSheet1.Workbook.Names.AddExternalVariable("x", "Sheet1!A2");
spreadSheet1.Workbook.Names.AddExternalVariable("y", "Sheet1!B2");
spreadSheet1.Workbook.Names.AddExternalVariable("z", "Sheet1!C2");
// Add the external variable to a separate cell.
spreadSheet1.Workbook.Worksheets[1].Cells["B1"].Formula = "\"The total sales of $\" & y & \" were made by \" & x & \" in the \" & z & \" country.\"";

VB

' Add another sheet in the workbook.
Dim newWorksheet As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
' Add an external variable to your workbook that references the cell value.
spreadSheet1.Workbook.Names.AddExternalVariable("x", "Sheet1!A2")
spreadSheet1.Workbook.Names.AddExternalVariable("y", "Sheet1!B2")
spreadSheet1.Workbook.Names.AddExternalVariable("z", "Sheet1!C2")
' Add the external variable to a separate cell.
spreadSheet1.Workbook.Worksheets(1).Cells("B1").Formula = """The total sales of $"" & y & "" were made by "" & x & "" in the "" & z & "" country."""