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).

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , , , , , , , , , , . Bookmark the permalink.

13 Responses to Working With Range.FormulaArray In VBA

  1. Doug Glancy says:

    What a thorough and useful post. I haven’t ever coded an array formula, but when I do, I’m coming back here. I agree about trying to write R1C1 formulas. I did it a few times when I was first coding and I felt like I needed a headlamp and a compass to keep from getting lost.

    Like

    • Colin Legg says:

      Thanks, Doug – much appreciated. Let us know how you get on and please feel free to post any FormulaArray information I’ve missed which you think might help someone.

      Like

  2. Gus Orchard says:

    Colin, this is a very useful post but I have a query. I have a spreadsheet that downloads a variable amount of data from a CSV file and after much sifting of the data produces a Pivot Table. I end up with 5 worksheets and have an array formula in the 5th worksheet that references the 1st. As the number of rows in the 1st sheet varies, I would like to be able to produce an array formula that references this 1st sheet without my having to intervene. The following is the code and I would like to be able to change the reference to row 21023 to “LastRowA” which is a name I have given the last used row in the 1st sheet.

    Selection.FormulaArray = “=INDEX(‘Tmac File output’!$A$2:$F$21023,MATCH(1,(TEXT(A2,””dd/mm/yyyy””)=’Tmac File output’!$A$2:$A$21023)*(TEXT(B2,””hh:mm:ss””)=’Tmac File output’!$B$2:$B$21023),0),6)”

    I have made various attempts to do this but always end up with a bug in the macro. If you could give me any help in resolving this, that would be great.

    Thanks in advance

    Gus Orchard

    Like

    • Colin Legg says:

      Hi Gus,

      Thanks for your comment. If I understand you correctly, LastRowA is a variable in your VBA code, so let’s use this as a starting point:


      Sub test()

      Dim LastRowA As Long

      'code to work out LastRowA
      '''
      '''----
      '''
      LastRowA = 21023

      Selection.FormulaArray = "=INDEX('Tmac File output'!$A$2:$F$21023," & _
      "MATCH(1,(TEXT(A2,""dd/mm/yyyy"")='Tmac File output'!$A$2:$A$21023)*" & _
      "(TEXT(B2,""hh:mm:ss"")='Tmac File output'!$B$2:$B$21023),0),6)"

      End Sub

      To use the variable in your formula string, you have to concatenate the value it holds. You can do this in two steps.
      First of all, surround each occurence of the value you want to replace (21023) with ” & and & ”

      Selection.FormulaArray = "=INDEX('Tmac File output'!$A$2:$F$" & 21023 & "," & _
      "MATCH(1,(TEXT(A2,""dd/mm/yyyy"")='Tmac File output'!$A$2:$A$" & 21023 & ")*" & _
      "(TEXT(B2,""hh:mm:ss"")='Tmac File output'!$B$2:$B$" & 21023 & "),0),6)"

      This code won’t work yet if you follow the very good practice of having an Option Explicit statement at the top of your code module. Next, the second step is to replace the 21023 with your variable name:

      Selection.FormulaArray = "=INDEX('Tmac File output'!$A$2:$F$" & LastRowA & "," & _
      "MATCH(1,(TEXT(A2,""dd/mm/yyyy"")='Tmac File output'!$A$2:$A$" & LastRowA & ")*" & _
      "(TEXT(B2,""hh:mm:ss"")='Tmac File output'!$B$2:$B$" & LastRowA & "),0),6)"

      And this code will do the job because VBA will coerce your VBA Long variable to a String type. I’m not a fan of implicit data type conversion in VBA so (in my opinion) you can put the icing on the cake by explicitly casting your Long variable to a string by using the CStr() function, ending up with this:


      Sub test()

      Dim LastRowA As Long

      'code to work out LastRowA
      '''
      '''----
      '''
      LastRowA = 21023

      Selection.FormulaArray = "=INDEX('Tmac File output'!$A$2:$F$" & CStr(LastRowA) & "," & _
      "MATCH(1,(TEXT(A2,""dd/mm/yyyy"")='Tmac File output'!$A$2:$A$" & CStr(LastRowA) & ")*" & _
      "(TEXT(B2,""hh:mm:ss"")='Tmac File output'!$B$2:$B$" & CStr(LastRowA) & "),0),6)"

      End Sub

      I hope that helps.

      Like

  3. Wayne Zander says:

    Excellent! I’ve been looking for this information for quite a while.

    I have an access report that makes extensive use of matrix math/formula arrays and another on the way. These use the squiggly brackets/braces “{}” in about 80 or so cells. If you don’t enter them all with the Ctrl+Shift+Enter to get the {}’s then you have a bunch of debugging to do. I’d really prefer to call a function where I only need to debug only 1 set of logic rather than 90 different ones.

    Like

  4. Pingback: Incriment veriable in a VBA formula - Page 2

  5. Pingback: Exclude Numbers From A List

  6. Pingback: IF statement in VBA

  7. Pingback: .FormulaArray to insert array formula within a table

  8. Pingback: FormulaArray in VBA

  9. Had to change array sizes and formulaes in existing worksheet with arrays. The thing that worked:

    Public Function CellRef(R As Long, C As Long) As String ‘ returns A1 notation of R1C1 cell address
    CellRef = vbNullString
    On Error GoTo HandleError:
    CellRef = Replace(Replace(Mid(Application.ConvertFormula(“=R” & R & “C” & C, XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1), 2), “$”, “”), “=”, “”)
    Exit Function
    HandleError:
    End Function

    Public Function iff(condCheck As Boolean, ifTrue As Variant, ifFalse As Variant) As Variant ‘ ternary operator workaround
    If condCheck Then
    iff = ifTrue
    Else
    iff = ifFalse
    End If
    End Function

    Sub set_formulas()
    Dim x As Long, x0 As Long, isNight As Long, xShift As Long
    x = Worksheets(2).Range(“AO1”).Column
    x0 = x
    Worksheets(3).Activate ‘ set initial variables

    For x = x0 To Worksheets(2).Range(“KB1”).Column
    Debug.Print x
    isNight = (iff(((x – x0) Mod 8) >= 4, 1, 0))
    xShift = Int((x – x0) / 8) * 5 + ((x – x0) Mod 4) ‘ calculating shifts for formulas
    For i = 1 To 109
    ‘ try clearing all arrays that are in the range we’re trying to fill
    With Worksheets(3).Range(CellRef(4 + i + 105 * isNight, 2 + xShift))
    If .HasArray Then
    Debug.Print “Trying to clear array: ” & .CurrentArray.Address
    .CurrentArray.ClearContents ‘ cleared!
    End If
    End With
    Next i
    Worksheets(3).Range(CellRef(4 + 105 * isNight, 2 + xShift) & “:” & CellRef(104 + 105 * isNight, 2 + xShift)).FormulaArray = “=IFERROR(INDEX(Òåõäàííûå!$B17:B316,SMALL(IF(“”””Òåõäàííûå!” & CellRef(17, x) & “:” & CellRef(316, x) & “,ROW(Òåõäàííûå!$B17:B316)-16,””””),ROW()-” & (3 + 105 * isNight) & “)),””””)”
    ‘ finally create a new formula array with the range
    Next x

    End Sub

    Like

  10. Pingback: VBA: These blog posts cover hard topics on working with array formulas in Excel | >keen /c /o /d /e

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s