Range.ArrayFormula Property

Gets or sets the array formula associated with the cell or cell range.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v18.2.Core.dll

Declaration

C#

string ArrayFormula { get; set; }

VB.NET

Property ArrayFormula As String

Property Value

Type	Description
String	A string that is the array formula.

Remarks

An array formula is a special kind of formula used to perform calculations with arrays of cells. For detailed information, see the Array Formulas topic.

Example

This example demonstrates how to create an array formula.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-e4655

FormulaActions.cs

// Create an array formula that multiplies values contained in the cell range A2 through A11 
// by the corresponding cells in the range B2 through B11, 
// and displays the results in cells C2 through C11.
worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = "=A2:A11*B2:B11";

// Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
// and displays the results in cells D2 through D11.
worksheet.Range["D2:D11"].ArrayFormula = "=C2:C11*2";

// Create an array formula that multiplies values contained in the cell range B2 through D11, 
// adds the results, and displays the total sum in cell D12.
worksheet.Cells["D12"].ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)";

// Re-dimension an array formula range:
// delete the array formula and create a new range with the same formula.
if (worksheet.Cells["C13"].HasArrayFormula) {
    string af = worksheet.Cells["C13"].ArrayFormula;
    worksheet.Cells["C13"].GetArrayFormulaRange().ArrayFormula = string.Empty;
    worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = af;
}

FormulaActions.vb

' Create an array formula that multiplies values contained in the cell range A2 through A11 
' by the corresponding cells in the range B2 through B11, 
' and displays the results in cells C2 through C11.
worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = "=A2:A11*B2:B11"

' Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
' and displays the results in cells D2 through D11.
worksheet.Range("D2:D11").ArrayFormula = "=C2:C11*2"

' Create an array formula that multiplies values contained in the cell range B2 through D11, 
' adds the results, and displays the total sum in cell D12.
worksheet.Cells("D12").ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)"

' Re-dimension an array formula range:
' delete the array formula and create a new range with the same formula.
If worksheet.Cells("C13").HasArrayFormula Then
    Dim af As String = worksheet.Cells("C13").ArrayFormula
    worksheet.Cells("C13").GetArrayFormulaRange().ArrayFormula = String.Empty
    worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = af
End If

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the ArrayFormula property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-use-excel-add-ins-in-the-spreadsheet-control-e5012/CS/SpreadsheetAddIn/Form1.cs#L51

    DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
    worksheet.Range["E4:E8"].ArrayFormula = "=SPHEREMASS(D4:D8, C4:C8)";
}

VB.NET

how-to-use-excel-add-ins-in-the-spreadsheet-control-e5012/VB/SpreadsheetAddIn/Form1.vb#L47

    Dim worksheet As DevExpress.Spreadsheet.Worksheet = workbook.Worksheets(0)
    worksheet.Range("E4:E8").ArrayFormula = "=SPHEREMASS(D4:D8, C4:C8)"
End Sub