Excel VBA Array Formula: How to Enter and Manage It

VBA & macros · Updated November 21, 2026

In Excel VBA, use Range.FormulaArray to enter a traditional, Ctrl+Shift+Enter array formula, or use Range.Formula2 to enter a modern dynamic-array formula that spills into neighboring cells. Choose the method that matches the formula: older fixed-size array calculations need FormulaArray, while Microsoft 365 spill formulas such as FILTER are generally entered in one starting cell with Formula2.

What Excel Means by an Array Formula

An array is a collection of values, such as a row, column, or rectangular range. An array formula performs a calculation on one or more collections of values and may return either a single result or multiple results. For example, multiplying corresponding quantities and prices produces one result for each row; summing those products produces one total.

“Array formula” can refer to two different Excel behaviors. Traditional array formulas are fixed-size formulas that, in older Excel versions, users confirmed with Ctrl+Shift+Enter. Dynamic-array formulas are the newer behavior: a formula entered in one cell can return several values, which spill into adjacent cells automatically. VBA can enter either type, but they use different properties and should not be treated as interchangeable.

There is also a separate VBA concept: a VBA array is a variable that stores values in code. A worksheet array formula is a formula written into a cell. A macro can work with both, but setting a VBA array variable does not itself create an Excel formula.

Choose Between FormulaArray and Formula2

Use FormulaArray when you specifically need a traditional array formula, for example to support an older workbook or preserve an existing fixed-size calculation. With legacy array formulas, the formula’s result area is selected and managed as a group. Editing one cell in that group independently is normally not allowed.

Use Formula2 for current Excel formulas that return dynamic arrays. Enter the formula in the top-left cell of the intended output area and let Excel determine the spill range. Do not assign the same spill formula separately to every cell in its output; that can create duplicate calculations or interfere with spilling. The cells that the result needs to occupy must be clear.

For a calculation that returns only one number, an array formula may not be necessary. A standard function such as SUMPRODUCT can often express a row-by-row multiplication and total directly. Prefer the simplest formula that meets the workbook’s compatibility and maintenance needs.

Enter a Traditional Array Formula with VBA

Suppose A2:A4 contains quantities 2, 3, and 4, while B2:B4 contains prices 10, 10, and 3. With those values in place, the worksheet expression =A2:A4*B2:B4 produces the three results 20, 30, and 12. In legacy Excel, that is a three-cell array result, so the destination range must contain three cells.

In the VBA editor, insert a standard module and assign the formula to the full destination range. The following macro expects a worksheet named Data and writes the calculated results to C2:C4. Save a copy of the workbook before running it: macros cannot be undone with Ctrl+Z.

Option Explicit

Sub EnterLegacyArrayFormula()
    Dim ws As Worksheet

    ' Use the worksheet that contains the source values.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Enter one fixed-size array formula across three cells.
    ws.Range("C2:C4").FormulaArray = "=A2:A4*B2:B4"
End Sub

After the macro runs, C2:C4 contains 20, 30, and 12, respectively. The result depends on the assumed source values and on the Data sheet existing in the same workbook as the macro. If the source ranges are different lengths, or the destination range has the wrong number of rows, adjust the formula and destination together.

You can also enter a traditional array formula manually. Select the intended result range, type the formula, and confirm it with Ctrl+Shift+Enter in Excel for Windows. On Excel for Mac, the corresponding legacy shortcut is Control+Shift+Return; keyboard layouts and versions can affect the key labels. Excel displays braces around a legacy array formula in the formula bar, but do not type those braces yourself.

Enter a Dynamic-Array Formula with VBA

Dynamic arrays are usually simpler to place: VBA writes the formula in one cell, and Excel spills its results. For example, with records in A2:B100, names in column A, and numeric scores in column B, the formula =FILTER(A2:B100,B2:B100>0,"No matches") returns rows whose score is greater than zero. If no rows meet the condition, the formula displays No matches.

FILTER is available in Microsoft 365 and Excel 2021 or later. In a VBA string, the quotation marks around the fallback text must be doubled so that Excel receives ordinary quotation marks. Check that the cells below and to the right of the formula cell are empty enough for the returned rows and columns to spill.

Save a copy before running this macro too. It places a formula in D2 on the Data sheet; it does not clear or overwrite other cells that might block the spill.

Option Explicit

Sub EnterDynamicArrayFormula()
    Dim ws As Worksheet

    ' Use the worksheet that holds the records.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Enter the formula once; Excel calculates the spill range.
    ws.Range("D2").Formula2 = "=FILTER(A2:B100,B2:B100>0,""No matches"")"
End Sub

If the formula returns five matching records, the output occupies five rows and two columns starting at D2. The spill range can change when source values change. A blocked spill usually produces a #SPILL! error rather than silently replacing the existing contents.

How FormulaArray and Formula2 Differ

FormulaArray is the VBA property for a traditional array formula. It is designed around a fixed result range. A notable limitation is that the formula text assigned through FormulaArray cannot exceed 255 characters, which can make it awkward for long expressions. When a formula is too long, consider simplifying it, using helper cells, or using a suitable modern formula instead.

Formula2 is designed for Excel’s newer formula behavior, including dynamic arrays and formulas that do not apply the older implicit-intersection behavior in the same way. It is generally the right property for modern formulas such as FILTER. It does not mean every formula returns an array: Formula2 can also enter an ordinary formula that returns one value.

The older Formula property remains useful for conventional formulas and compatibility with workbooks that do not rely on dynamic-array behavior. The key practical difference is not merely the formula’s appearance: a formula entered with Formula2 can spill, whereas a legacy array formula is associated with a selected fixed-size range.

Formula Text, Cell References, and Calculation

In VBA, Formula and Formula2 use Excel’s standard formula notation, with English function names and comma separators, regardless of many regional display settings. FormulaLocal is a different property that uses the local language and regional formula conventions. For code intended to work across computers with different language settings, Formula or Formula2 is usually easier to maintain than FormulaLocal.

Use the right reference style for the task. A formula such as =A2*B2 uses relative references, so copying it down adjusts the row numbers. An expression that refers to a fixed input cell may need an absolute reference such as =$F$1*A2. FormulaR1C1 and Formula2R1C1 are alternatives when code needs to describe formulas relative to the destination cell rather than construct A1-style references.

Excel normally recalculates formulas according to the workbook’s calculation settings. If calculation is set to manual, a newly written formula may not immediately show an updated result. In that case, recalculate the workbook using Formulas > Calculate Now, or set the application’s calculation mode deliberately in VBA. Avoid changing calculation settings without restoring the original setting if the macro is part of a larger workflow.

Common Problems and How to Fix Them

  • Wrong range size: A legacy array formula intended to return three rows needs a three-row destination. Recheck the source dimensions and the selected destination before assigning FormulaArray.
  • A blocked spill: A dynamic formula may show #SPILL! if a value, merged cell, or other obstruction is in the required output area. Clear or move the obstruction; do not write the formula into every cell of the spill area.
  • Unsupported function: A workbook opened in an older Excel version may not recognize a modern function such as FILTER. Use a compatible alternative or ensure users have a supported Excel version.
  • Formula text error: Check the initial equals sign, balanced parentheses, separators, and doubled quotation marks inside VBA strings. A formula that works when typed into a cell can still fail in code if its string is assembled incorrectly.
  • Unexpected values: Confirm that the source cells contain the expected data types and that the formula references the intended sheet. Text that looks like a number, blank cells, and incorrect range boundaries can change results.
  • FormulaArray length limit: If the legacy formula exceeds 255 characters, break the work into helper calculations or evaluate whether a modern formula entered through Formula2 is appropriate.

Testing and Safe Macro Use

Before using a macro on important workbooks, test it on a copy with a small, known dataset. Verify both the formula in the destination cell and the calculated results. For a multi-cell legacy formula, inspect the entire selected output range; for a dynamic formula, inspect the spill range and confirm that its size changes as expected when source records change.

Use explicit worksheet references rather than relying on whichever sheet is active. The examples use ThisWorkbook, meaning the workbook containing the VBA code. If a macro is stored in another workbook but should edit the active workbook, that choice must be changed intentionally. Handle a missing worksheet name or an unexpected destination range when turning a short example into a production macro.

Macros can change workbook contents and cannot be reversed with Ctrl+Z. Save a backup before running one, and use a macro-enabled workbook format such as .xlsm when the VBA code needs to be retained. On Windows, Alt+F11 opens the VBA editor; on Mac, the shortcut is commonly Option+F11, sometimes with the Fn key depending on the keyboard. The Developer tab can also open the Visual Basic editor.

Alternatives to Array Formulas

For a single total from corresponding rows, SUMPRODUCT may be clearer than a legacy array formula. With quantities in A2:A4 and prices in B2:B4, =SUMPRODUCT(A2:A4,B2:B4) returns 62 for the example values 2, 3, 4 and 10, 10, 3: the row products are 20, 30, and 12, and their sum is 62. SUMPRODUCT is not a universal replacement for every array formula, but it is a convenient choice for this type of total.

For modern Excel tasks involving filtering, sorting, or extracting distinct values, dynamic-array functions may avoid a macro entirely. FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later. A worksheet formula can be easier for other users to inspect and maintain than VBA, while VBA is useful when formulas must be installed repeatedly, conditionally, or as part of a larger automated process.

Frequently Asked Questions

How do I insert an array formula in Excel using VBA?
Use Range.FormulaArray for a traditional fixed-size array formula and assign it to the full output range. For a modern formula that spills, place it in the top-left cell with Range.Formula2.

Do I still need Ctrl+Shift+Enter in Microsoft 365?
Not for modern dynamic-array formulas. Enter those normally in a cell, or set them with Formula2. Ctrl+Shift+Enter is relevant when working with a traditional legacy array formula.

Can FormulaArray create a formula that spills?
Use Formula2 for modern spill behavior. FormulaArray is intended for traditional array formulas associated with a fixed-size range, so it is not the appropriate property for a dynamic spill formula.

Why does my formula show #SPILL! after VBA enters it?
Excel cannot place the complete dynamic result in the required cells, usually because something is blocking part of the spill range. Check for existing values, merged cells, or other obstructions around the formula cell.

Can I use Formula2 in older Excel versions?
Formula2 is intended for Excel’s newer formula behavior. Older releases may not support it or the dynamic-array functions used in the formula. If the workbook must work in older Excel, use compatible formulas and test in the oldest version your users need.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.