How to: Manage Static (Legacy) Array Formulas

• Nov 29, 2024
• 4 minutes to read

Static or legacy array formulas are the main array formulas in Microsoft Excel 2019 and earlier. These formulas are static and must be entered into a range of cells that match the size of the output. Excessive values are not shown if the number of returned values exceeds the number of cells. If the number of cells is greater than the number of values, excessive cells are not left blank – they are filled with the same values repeatedly.

Refer to the following article for information and code snippets on new dynamic array formulas: Array Formulas

Create an Array Formula

The CellRange.ArrayFormula property allows you to specify the array formula for a range of cells. The range of cells containing the same array formula is treated as a single entity. You can only modify data for the entire range.

The Worksheet.ArrayFormulas property returns the collection of legacy array formulas (the ArrayFormula objects) in the current worksheet. You can determine the ArrayFormula.Range of cells that contain the array formula, as well as the text of the ArrayFormula.Formula itself.

This example creates a legacy array formula:

C#

// 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["C2:C11"].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["C2:C11"].ArrayFormula = af;
}

VB.NET

' 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("C2:C11").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("C2:C11").ArrayFormula = af
End If

Modify an Array Formula

Array formulas can be modified for the entire formula range only. To change the formula, get its range via the Cell.GetArrayFormulaRange method or the ArrayFormula.Range property. You can also access the required range using the Worksheet.Range property. The CellRange.ArrayFormula property will allow you to get or set the array formula for the created range.

For each cell that belongs to the array formula range, the CellRange.ArrayFormula and the CellRange.Formula properties return a formula string. An System.InvalidOperationException exception occurs when changing the Value or Formula property of any cell in the array range.

To check whether the cell is the top left cell in the range, use the Cell.IsTopLeftCellInArrayFormulaRange property.

Delete an Array Formula

To delete an array formula, assign an empty string to the CellRange.ArrayFormula property of the entire array formula range.