Insert an Array Formula Using VBA

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:

  1. While in the active worksheet that contains the data set press Alt + F11 to open the Visual Basic Editor.
  2. Click Insert >> Module to insert a new module.
Graphical user interface, application

Description automatically generated
  1. Type in the module the following procedure:
  1. Save the procedure and save the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the procedure and press F5 to run the code.
  3. Press Alt + F11 to switch back to the active workbook.

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

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:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Click Insert >> Module to insert a new module.
  3. Type the following procedure in the module.
  1. Save the procedure and save the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the procedure and press F5 to run the code.
  3. Press Alt + F11 to switch back to the active workbook.

The following array formula has been inserted in cell E13:

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba