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

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.

LikeLike

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.

LikeLike

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

LikeLike

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

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

LikeLike

Hi Colin

That’s excellent. Problem solved – thank you!

Regards

Gus

LikeLike

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.

LikeLike

Pingback: Incriment veriable in a VBA formula - Page 2

Pingback: Exclude Numbers From A List

Pingback: IF statement in VBA

Pingback: .FormulaArray to insert array formula within a table

Pingback: FormulaArray in VBA

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

LikeLike

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

Pingback: #Excel Super Links #130 – shared by David Hager | Excel For You