Working With Range.FormulaArray In VBA

1 Introduction

The Range.FormulaArray property is used to set or return the array formula of a range and works in a very similar way to the more familiar Range.Formula property. However, because of the fact that it is working in relation to array formulas, there are more restrictions to consider and it is slightly more difficult to use. In the context of this article, an ‘array formula’ can be considered to mean a formula which has been entered into the formula bar using CTRL+SHIFT+ENTER rather than ENTER, so that it has been enclosed by parentheses { }.

2 Returning A Formula From A Range

If you want to return a formula from a single cell, the Range.Formula and Range.FormulaArray properties both return exactly the same result regardless of whether that cell contains an array formula or not.

However, they return different results when they are applied to a contiguous, multi-cell range. If the range is an array range (a block of cells that shares a single array formula) then the FormulaArray property returns that formula. If the range is not an array range but all of the cells contain identical formulas, then the FormulaArray property will also return that common formula. If the range is not an array range and the cells do not contain identical formulas, then the FormulaArray property returns Null. In all three scenarios, the Formula property will return an array of Variants, with each element of the array representing a formula from each cell within the range.

3 Setting An Array Formula In A Range

According to the Remarks section in ‘Range.FormulaArray Property’ topic in the VBA help file, R1C1 reference style rather than A1 reference style should be used when setting an array formula. This isn’t strictly true, although it may make problem-shooting runtime errors more straightforward. I find A1 notation much easier to use, so I will use it in all the following examples and I’ll discuss the R1C1 vs. A1 reference style question in the problem-shooting section later on.

3.1 Setting A Single-Cell Array Formula

The two points to note are that the = sign at the beginning of the string is optional and that you should not include the parentheses { } which will automatically surround the array formula once it has been assigned.

'put array formula {=FREQUENCY(A2:A10, B2:B4)} into cell E2
Sheet1.Range("E2").FormulaArray = "=FREQUENCY(A2:A10, B2:B4)"

 3.2 Setting A Multi-Cell Array Formula

If the intention is for the block of cells to be an array range (sharing a single formula) then it is as straightforward as the previous example:

'put array formula {=A2:A10="hello"} into cells C2:C10
Sheet1.Range("C2:C10").FormulaArray = "=A2:A10=""hello"""

If the intention is for each cell in the block to have its own array formula then a little bit more work has to be done. Let’s compare a few different options using this array formula as an example:

{=MAX(IF((($E$2:$E$10=A2)+($F$2:$F$10=B2))=1,$G$2:$G$10))}

This formula returns the maximum value in I2:I10 where either (but not both) of the corresponding cells in column G equals A2 or column H equals B2.

Because we want the A2 and B2 references to adjust as we ‘fill’ the array formula down the column, we cannot use the Range.FormulaArray property as we have previously.

The first option is to use a loop, for example:

Sub Option1()

    Dim r As Long

    For r = 2 To 5
        Sheet1.Cells(r, 3).FormulaArray = _
          "=MAX(IF((($E$2:$E$10=A" & CStr(r) & ")+($F$2:$F$10=B" & CStr(r) & "))=1,$G$2:$G$10))"
    Next r

End Sub

Every formula will be calculated irrespective of calculation settings with this option.

The second option is to populate the first cell and then copy/paste or fill it down:

Sub Option2()

    Sheet1.Range("C2").FormulaArray = _
        "=MAX(IF((($E$2:$E$10=A2)+($F$2:$F$10=B2))=1,$G$2:$G$10))"

    Sheet1.Range("C2:C5").FillDown

End Sub

This method will copy not only fill down the formulas but also the formats etc., which may be an advantage or a disadvantage depending on the situation. If calculations are set to manual then the ‘filled in’ cells will not be calculated. In my testing this method seems to be the fastest even with calculations set to automatic.

The third option is to populate the first cell and then copy it onto the clipboard and paste special formulas:

Sub Option3()

    Sheet1.Range("C2").FormulaArray = _
        "=MAX(IF((($E$2:$E$10=A2)+($F$2:$F$10=B2))=1,$G$2:$G$10))"

    Sheet1.Range("C2").Copy
    Sheet1.Range("C3:C5").PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False

End Sub

This avoids copying down the formats etc. but in my testing this method seems to be much slower than all the others. If calculations are set to manual then the formulas that have been pasted will not be calculated.

A final option is as follows:

Sub Option4()

    With Sheet1.Range("C2:C5")
        'step 1
        .Formula = "=MAX(IF((($E$2:$E$10=A2)+($F$2:$F$10=B2))=1,$G$2:$G$10))"

        'step 2
        .FormulaArray = .FormulaR1C1
    End With

End Sub

The formula assignment in step 1 can be done with either the Formula or FormulaR1C1 properties. However, the FormulaR1C1 property must be used in step 2 because using the Formula property would cause the relative references to become distorted down the range. This approach performs fairly similarly to the Option 1 loop approach.

3.3 Problem-Shooting

When you’re trying to use the Range.FormulaArray property you may get the following error:

Run-time error ‘1004’: ‘Unable to set the FormulaArray property of the Range class’

 The message doesn’t contain a lot of useful information so determining the cause of the problem can be quite tough. Here are some reasons why you may be getting this error message:

3.3.1 You Are Trying To Change Part Of An Array Range

For example, this code will fail:

'create an array range
Sheet1.Range("C2:C10").FormulaArray = "=A2:A10=""hello"""

'try to change part of an array range gives an ERROR
Sheet1.Range("C2").FormulaArray = "=A2:A10=""hello"""

You have to clear the array range first or change the entire array range at the same time. You can determine if a cell is part of an array range as follows:

With Sheet1
    'create an array range
    .Range("C2:C10").FormulaArray = "=A2:A10=""hello"""

    With .Range("C2")
        'check if C2 is part of an array range
        If .HasArray Then

            'what is the full array range?
            MsgBox .CurrentArray.Address
        End If
    End With
End With

3.3.2 You Are Trying To Put An Array Formula Into A Merged Cell

It is possible to put an array formula into a cell and then merge that cell with other cells, but you cannot put an array formula into a cell that has already been merged. For example, this code will fail:

'create some merged cells
Sheet1.Range("C2:C10").Merge

'try to set an array formula gives an ERROR
Sheet1.Range("C2").FormulaArray = "=A2:A10=""hello"""

You can check if a cell is part of a merged range as follows:

With Sheet1
    'create some merged cells
    .Range("C2:C10").Merge

    'check if C2 is part of a merged range
    With .Range("C2")
        If .MergeArea.Address = .Address Then
            MsgBox "Cell not merged"
        Else
            MsgBox "Cell is merged, merged range = " & .MergeArea.Address
        End If
    End With
End With

3.3.3 Your Array Formula Contains A Syntax Error Such As A Missing Or Invalid Argument

With Sheet1.Range("E2")
    'this will give an error because argument in SUM() function missing
    .FormulaArray = "=SUM()"

    'this will give an error because SUMIF() cannot accept an array data type
    'passed into its 1st or 3rd parameters: http://support.microsoft.com/kb/214286/
    .FormulaArray = "=SUMIF((A2:A19=1)*(B2:B19),B2,C2:C19)"
End With

3.3.4 Your Array Formula Exceeds 255 Characters

This issue is described on the following MS Support article:

http://support.microsoft.com/kb/213181

More specifically:

• If you are using A1 notation then the R1C1 equivalent must be less than 255 characters. I picked this information up from MS MVP Rory Archibald.
• If you are using R1C1 notation then the formula must be less than 256 characters.

A workaround is using the Range.Replace() method as demonstrated at DailyDoseOfExcel:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

I always use a variable to build up and hold the string representing the array formula I want to apply. I find it makes my code easier to read and debug. Another useful tip is that the Application.ConvertFormula() method can be used to easily convert strings between A1 and R1C1 notation (as well as toggling relative or absolute referencing).