Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Using and creating array formulae

written by Helen Toomik - Last updated Oct 2004

This article continues where "Passing arrays to and from functions" left off. After reading that one, you should be able to write functions that deal with arrays. If not, you may want to have a quick look at that article.

Here, we look at what we can do with array functions and array formulas in Excel VBA. All the examples from both articles are available in a sample workbook.

Worksheet functions that return arrays

Perhaps you have noticed that some functions in Excel can return an array of values - such as TRANSPOSE() and FREQUENCY(). (If you're not familiar with array formulas, have a look at the last section of this article, where I give a quick overview of how they work.)

Let's create one of our own - let's make the ReverseArray() function that we wrote previously (here) into a UDF called ReverseRange() that can be called from a worksheet, referring to a worksheet range for the aryIn argument.

First of all we need to replace aryIn with a range reference, and get the contents of the range into an array. Then, we need to take into account two possibilities: single-cell ranges and multi-cell ranges. For a single-cell range, Range.Value will return a single value. For a range of several cells, Range.Value will return a 2D array (even if the range is a single row or column). So we'll need to adjust the code to handle both possibilities.

 
Public Function ReverseRange(rng As Range) As Variant() 
    Dim aryIn() As Variant 
    Dim a As Variant 
    Dim i As Integer, j As Integer 
    Dim temp() As Variant 
     
    If rng.Cells.Count = 1 Then 
        ReverseRange = Array(rng.Value) 
        Exit Function 
    End If 
     
    aryIn = rng.Value 
    ReDim temp(LBound(aryIn) To UBound(aryIn), _ 
    LBound(aryIn, 2) To UBound(aryIn, 2)) 
    For i = LBound(aryIn) To UBound(aryIn) 
        For j = LBound(aryIn, 2) To UBound(aryIn, 2) 
            temp(i, j) = aryIn(UBound(aryIn) + LBound(aryIn) - i, _ 
            UBound(aryIn, 2) + LBound(aryIn, 2) - j) 
        Next j 
    Next i 
    ReverseRange = temp() 
End Function 

Using array formulas

Array formulas are a powerful technique in Excel that allows you to perform calculations on entire arrays, instead of single values. It's often possible to replace an entire UDF or macro with a well-crafted array formula. The downside is that a worksheet with large amounts of array formulas will be slow to recalculate. Also, advanced array formulas tend to be difficult to read and debug. The topic is so broad and complex that I won't go into detail here about how to write them. I'll give a quick intro (for those who haven't encountered them before), give some links, and then focus on the VBA aspects of array formulas.

To enter an array formula, select the whole range where you want the output to be, type in your formula, and hit Ctrl+Shift+Enter to enter the formula. You can recognise an array formula by the braces { } that surround it. You don't type in the braces; they appear automatically if you enter the formula using Ctrl+Shift+Enter. For example, try this... Enter some values in A1:A4. Then select C1:F1, type in =TRANSPOSE(A1:A4) and hit Ctrl+Shift+Enter. The result will be the data in A1:A4 but transposed from a column into a row.

Note that you cannot change part of an array formula. If you want to make the output range smaller, for example, or delete cells that intersect with an array, you'll have to change the formula from an array formula to a normal one, make your changes, and enter the formula again. To change an array formula into a normal formula, select the whole array, hit F2 to edit the formula, and then Ctrl+Enter.

Links to a few good pages about array formulas:

To use an array UDF in a worksheet, the approach is the same as for any other array formula. Select the range where you want the output, type in your function name and arguments, and hit Ctrl+Shift+Enter.

Entering array UDFs using VBA code

You have probably encountered the Range.Formula property that you can use to enter formulas in worksheet cells:

Range("A2").Formula = "=A1+1" 

There is a similar property for entering array formulas - Range.FormulaArray - and it works just the way you would expect. Instead of entering our TRANSPOSE formula manually, we could have used the following VBA code:

Range("C1:F1").FormulaArray = "=TRANSPOSE(A1:A4)" 

Or for our own ReverseRange UDF:

Range("C1:C4").FormulaArray = "=ReverseRange(A1:A4)" 

As with the manual approach, you cannot change part of an array formula. You cannot enter a new array formula in a range that's already partly filled with an array formula. The solution is also the same - convert from array formula to normal formulas, change, and re-enter the array formula. Alternatively, delete the previous array formula and enter a new one.

For example, let us delete row 2 in our worksheet. Since we want the array formula to remain the same, we don't delete it completely - just convert it, and put it back.

 
Dim rng As Range 
Set rng = Range("C1").CurrentArray 
rng.Formula = rng.FormulaArray 
Rows(2).Delete 
rng.FormulaArray = rng.Cells(1, 1).Formula 

Note that in the last row, we're using rng.Cells(1, 1).Formula and not rng.Formula. If we used rng.Formula, a separate array formula would be entered in each cell. There's no real logic to that as far as I can see... that's just the way it works.

Note also that if C1 is not part of an array, you'll get an error ("No cells were found"). To pre-empt that, use Range.HasArray to check first if C1 is part of an array.

Examples of all this can be found in the sample workbook.