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.

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:

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.

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:

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:

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

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

15 Responses to Working With Range.FormulaArray In VBA

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.

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.

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:

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.