Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Creating and Using External Variable
In This Topic
    Creating and Using External Variable
    In This Topic

    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.

    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.

    C#
    Copy Code
    // 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";
    

    VB
    Copy Code
     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"
    
    See Also