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.