An array formula performs many calculations on one or more items in an array. This tutorial shows how to insert a multi-cell and single-cell array formula in a dataset using **Excel VBA**.

## Example

We use the following data set of car sales in our illustrations. The dataset is contained in **Sheet1** of our workbook.

## Insert a multi-cell array formula using Excel VBA

To insert a multi-cell array formula in range **E2:E11** we use the following steps:

- While in the active worksheet that contains the data set press
**Alt + F11**to open the**Visual Basic Editor**. - Click
**Insert >> Module**to insert a new module.

- Type in the module the following procedure:

1 2 3 |
Sub arrayFormula() Worksheets("Sheet1").Range("E2:E11").FormulaArray = "=C2:C11*D2:D11" End Sub |

- Save the procedure and save the workbook as a
**Macro-Enabled Workbook**. - Place the cursor anywhere in the procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch back to the active workbook.

The following array formula has been inserted in every cell in range **E2:E11**:

1 |
{=C2:C11*D2:D11} |

The braces around the formula show that it is an array formula.

## Insert a single-cell array formula using Excel VBA

To insert a single cell array formula in cell **E12** we use the steps below:

- Press
**Alt + F11**to open the**Visual Basic Editor**. - Click
**Insert >> Module**to insert a new module. - Type the following procedure in the module.

1 2 3 |
Sub arrayFormula2() Worksheets("Sheet1").Range("E12").FormulaArray = "=SUM(C2:C11*D2:D11)" End Sub |

- Save the procedure and save the workbook as a
**Macro-Enabled Workbook**. - Place the cursor anywhere in the procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch back to the active workbook.

The following array formula has been inserted in cell **E13**:

1 |
{=SUM(C2:C11*D2:D11)} |

**Explanation of the code**

The code that inserts the multi-cell and single-cell array formulas utilizes the **FormulaArray** property of the range object. This property is used to set the array formula of the specified ranges.

## Conclusion

This tutorial has explained how to insert a multi-cell and single-cell array formula in a dataset using Excel VBA. The Excel VBA code utilizes the **FormulaArray** property of the range object to set the array formulas.