How to Use Dynamic Array Formulas in Excel and VBA

Dynamic array formulas are a new feature in Excel that allow you to work with multiple values at the same time in a formula. Dynamic array formulas can return a range of values, called a spill range, that automatically expands or contracts to fit the data. Dynamic array formulas can also use new functions that are designed to work with arrays, such as UNIQUE, SORT, FILTER, SEQUENCE, and RANDARRAY.

In this article, we will learn how to use dynamic array formulas in Excel, and how to add them using VBA. We will cover the basic theory, the procedures, and a detailed example with real numbers. We will also explore some other approaches that are available for working with arrays in Excel.

An array is a collection of values that are stored in a single variable. For example, you can store the names of five cities in an array, like this:

Dim cities(1 To 5) As String

cities(1) = "New York"

cities(2) = "London"

cities(3) = "Paris"

cities(4) = "Tokyo"

cities(5) = "Sydney"

You can access the values in an array by using an index number, such as cities(1) or cities(5). You can also loop through the values in an array using a For Each or a For Next loop.

An array can be either static or dynamic. A static array has a fixed size that is determined when you declare the array. For example, the array cities above is a static array with a size of 5. A dynamic array has a variable size that can change during the execution of the code. You can declare a dynamic array by omitting the array size, like this:

Dim numbers() As Integer

To assign values to a dynamic array, you need to use the ReDim statement to resize the array to your desired size. For example, you can resize the array numbers to hold 10 values, like this:

ReDim numbers(1 To 10)

You can also use the ReDim Preserve statement to resize the array while keeping the existing values. For example, you can resize the array numbers to hold 15 values, without losing the previous 10 values, like this:

ReDim Preserve numbers(1 To 15)

A dynamic array formula is a formula that returns a dynamic array, or a spill range, as a result. A spill range is a range of cells that automatically fills with the values returned by the formula. For example, if you enter the formula =SEQUENCE(5) in cell A1, it will return a spill range of 5 cells, from A1 to A5, with the values 1, 2, 3, 4, and 5. You can refer to the spill range by using the spill reference, which is the address of the first cell followed by a hash sign (#). For example, the spill reference for the formula above is A1#.

You can use dynamic array formulas to perform calculations or operations on multiple values at once, without having to enter the formula in each cell. For example, you can use the formula =A1#*2 in cell B1 to multiply each value in the spill range A1# by 2, and return another spill range in B1:B5. You can also use dynamic array formulas to filter, sort, or extract unique values from a range of data, using the new functions that are designed for arrays.

Procedures

To add a dynamic array formula in Excel, you can simply enter the formula in a cell, and press Enter. Excel will automatically create a spill range for the formula, and adjust it if the data changes. You can also use the Formula Bar to enter or edit the formula, and see the spill reference for the formula.

To add a dynamic array formula using VBA, you need to use the Range.Formula2 property, or the Range.Formula2R1C1 property if you use R1C1 notation. These properties use array evaluation, which is the default behavior for dynamic array formulas. For example, you can use the following code to add the formula =UNIQUE(A1:A10) in cell B1, using VBA:

Range("B1").Formula2 = "=UNIQUE(A1:A10)"

If you use the Range.Formula property, or the Range.FormulaR1C1 property, Excel will use implicit intersection, which is the legacy behavior for formulas that return multiple values. Implicit intersection reduces many values to a single value, by adding the @ symbol to the formula. For example, if you use the following code to add the formula =UNIQUE(A1:A10) in cell B1, using VBA:

Range("B1").Formula = "=UNIQUE(A1:A10)"

Excel will add the @ symbol to the formula, and return #VALUE! error, like this:

=@UNIQUE(A1:A10)

To avoid this error, you should always use the Range.Formula2 property, or the Range.Formula2R1C1 property, when adding dynamic array formulas using VBA.

Example

To illustrate how to use dynamic array formulas in Excel and VBA, let’s look at a detailed example with real numbers. Suppose we have a table of sales data for different products and regions, like this:

We want to use dynamic array formulas to perform the following tasks:

• Filter the table to show only the sales for product A
• Sort the filtered table by sales in descending order
• Extract the unique regions from the filtered table
• Calculate the total sales for each region
• Create a summary table with the region and the total sales

To do this, we can use the following formulas in Excel:

• In cell E1, enter the formula =FILTER(A1:C10,A1:A10="A") to filter the table by product A. This will return a spill range of 3 rows and 3 columns, from E1 to G3, with the filtered data.
• In cell J1, enter the formula =SORT(E1#,3,-1) to sort the filtered table by sales in descending order. This will return a spill range of 3 rows and 3 columns, from J1 to L3, with the sorted data.
• In cell N1, enter the formula =UNIQUE(E2#) to extract the unique regions from the filtered table. This will return a spill range of 3 rows and 1 column, from N1 to N3, with the unique regions.
• In cell O1, enter the formula =SUMIF(E2#,N1#,G2#) to calculate the total sales for each region. This will return a spill range of 3 rows and 1 column, from O1 to O3, with the total sales.
• In cell Q1, enter the formula =N1#&" "&O1# to create a summary table with the region and the total sales. This will return a spill range of 3 rows and 1 column, from Q1 to Q3, with the summary table.

The result will look like this:

To do the same tasks using VBA, we can use the following code:

Sub AddDynamicArrayFormulas()
    'Filter the table by product A
    Range("E1").Formula2 = "=FILTER(A1:C10,A1:A10=""A"")"
    
    'Sort the filtered table by sales in descending order
    Range("J1").Formula2 = "=SORT(E1#,3,-1)"
    
    'Extract the unique regions from the filtered table
    Range("N1").Formula2 = "=UNIQUE(E2#)"
    
    'Calculate the total sales for each region
    Range("O1").Formula2 = "=SUMIF(E2#,N1#,G2#)"
    
    'Create a summary table with the region and the total sales
    Range("Q1").Formula2 = "=N1#&"" ""&O1#"
End Sub

This code will add the same dynamic array formulas as in Excel, and return the same results. You can run this code by pressing F5 in the Visual Basic Editor, or by assigning it to a button or a shortcut key.

Other approaches

Dynamic array formulas are not the only way to work with arrays in Excel and VBA. There are some other approaches that you can use, such as:

• Using array constants: An array constant is a set of values enclosed in curly braces, such as {1,2,3} or {“A”,“B”,“C”}. You can use array constants in formulas or in VBA code to create or assign arrays. For example, you can use the formula =SUM({1,2,3}) to calculate the sum of the array constant {1,2,3}, or you can use the code Dim letters As Variant letters = Array("A","B","C") to create an array variable with the array constant {“A”,“B”,“C”}.
• Using array formulas: An array formula is a formula that performs calculations on multiple values at once, and returns multiple values as a result. Unlike dynamic array formulas, array formulas do not create spill ranges, but require you to select the output range before entering the formula. You also need to press Ctrl+Shift+Enter to enter an array formula, instead of just Enter. For example, you can use the array formula =TRANSPOSE(A1:A10) to transpose the values in A1:A10, but you need to select 10 cells in a row, and press Ctrl+Shift+Enter to enter the formula.
• Using the Evaluate method: The Evaluate method is a VBA method that allows you to evaluate a formula or an expression, and return the result as a variant. You can use the Evaluate method to evaluate array formulas or expressions, and assign them to array variables. For example, you can use the code Dim numbers As Variant numbers = Evaluate("ROW(1:10)") to create an array variable with the values 1 to 10, by evaluating the array formula ROW(1:10).

These approaches have their own advantages and disadvantages, and you can choose the one that suits your needs best. However, dynamic array formulas are generally easier to use and more flexible than the other approaches, as they do not require you to select the output range, press special keys, or use VBA code. Dynamic array formulas also update automatically when the data changes, and can use the new functions that are designed for arrays.

In this article, we learned how to use dynamic array formulas in Excel and VBA, and how to add them using the Range.Formula2 property. We also learned the basic theory of arrays, and how to perform various tasks with dynamic array formulas, such as filtering, sorting, extracting unique values, calculating totals, and creating summary tables. We also explored some other approaches for working with arrays in Excel and VBA, such as using array constants, array formulas, and the Evaluate method.

Dynamic array formulas are a powerful and convenient feature in Excel that allow you to work with multiple values at the same time in a formula. Dynamic array formulas can return a range of values, called a spill range, that automatically expands or contracts to fit the data. Dynamic array formulas can also use new functions that are designed to work with arrays, such as UNIQUE, SORT, FILTER, SEQUENCE, and RANDARRAY.