Use the FormulaArray property of the Range object to enter array formulas in a cell from VBA. The array formula must be 255 characters or less or you’ll see the ever-helpful “Unable to set the FormulaArray property of the Range class” error.
Dave sent me a solution to this problem that’s shown below. It replaces part of the formula with a place holder, then uses the Replace method of the Range object to add the rest. Apparently Replace doesn’t have such limitations as FormulaArray does.
By the way, he uses JWalk’s cool calendar formula (as seen in the Ugly Formulas comments) as his example. I love that formula, although that doesn’t really have anything to do with this post.
”’ If the long formula can be broken into parts, where the second part can
”’ be replaced by a dummy function, this approach can be used…
Public Sub LongArrayFormula()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
“MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
“X_X_X())”
theFormulaPart2 = “DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
With ActiveSheet.Range(“E2:K7”)
.FormulaArray = theFormulaPart1
.Replace “X_X_X())”, theFormulaPart2
.NumberFormat = “mmm dd”
End With
End Sub
”’ be replaced by a dummy function, this approach can be used…
Public Sub LongArrayFormula()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
“MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
“X_X_X())”
theFormulaPart2 = “DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
With ActiveSheet.Range(“E2:K7”)
.FormulaArray = theFormulaPart1
.Replace “X_X_X())”, theFormulaPart2
.NumberFormat = “mmm dd”
End With
End Sub
Nice work, Dave. Thanks for the suggestion.
While I consider myself fairly proficient at Excel in general, I get all weak in the knees when confronted with Array Formulas. Is there anywhere I can find a good tutorial dealing exclusively with creating and using Array Formulas?
Thank you.
David
http://www.cpearson.com/excel/array.htm
and
http://www.dicks-blog.com/archives/2004/04/05/anatomy-of-an-array-formula/
ara a couple of places you can start.
I was wondering whether you guys can help me out with this one
setup: win2000, excel 2000
problem:
i have a long array formula
X_TABLE_FORMULA = “=SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$H$5:$H$1130;0)))/SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$B$5:$B$1130;0)))”
of course entering it via:
Selection.FormulaArray = X_TABLE_FORMULA
returns the well known error message.
however if i split it down, e.g.
X_TABLE_FORMULA_1 = “=IF(B$1=HISTORIC_DATA!$E$5:$E$1130;””YO””;””NO””)”
returns this error. you guys have any idea?
any comment, suggestion is highly appreciated.
I guess replacing the semicolons with comma’s should help fix this.
Has anyone got a technique for doing this with chart series formulas? I’m trying to delink a chart’s data which I can do by hand because of the 1024 formula limit, but from VBA I’m restricted to 255.
I can’t use the Replace method as the ChartSeries.Formula property is not a Range object. Any ideas would be really useful, as I’m stumped…
Paul –
Please pardon me for plugging my own site. I demonstrate the use of WorksheetFunction.Substitute to change the chart series formula on this page:
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
I also discuss the ins and outs of delinking chart series here:
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
– Jon
Hi Jon,
Thanks for your reply. I had actually stumbled acorss your site whilst searching for an answer to this question.
Unfortunately your solution doesn’t work. The problem is that setting the formula property on a chart series with anything over 255 characters causes a failure. WorksheetFunction.Substitute just returns a string and so the problem still exists. The only way I’ve find to do it, is to use the technique that involves saving your chart in the user gallery, and then copying the chart from there.
Why does Excel (I’m using 97) have a 1024 character limit when a user types a formula in, and a 255 one from within VBA? It seems to be a silly limitation.
The stated limit of 1024 characters for the series formula is misleading, because each component of the formula (name, xvalues, values, and plot order) behaves as if it has a limit of around 255. The practical limit is therefore much lower than 1024.
Are you trying to use assign a VBA array to a series, or is there a reason you don’t want data in the worksheet? If it’s the former, dump the data into worksheet ranges, and point the series formula at these ranges.
Thanks for the speedy reply.
I’m just trying to delink the data as the chart is created in another workbook. There’s definitely some discrepancy between manually entering a formula and assigning it in VBA as I can hit F9 in the formula bar and it works fine, but by using the same formula from within VBA (or assigning an array, etc) it fails. I’ve also ensured that each section of the formula is less than 255 characters but setting the formula property still fails.
If possible, I’d rather not have the raw data stored anywhere apart from in the chart. I realise that I can store the data in another (possibly hidden) sheet, but it’s just frustrating when I have this much trouble automating something I can do by hand.
You can F9 the series formula, and it /might/ work (I just ran off a quick example, and it didn’t), but as soon as you touch the formula again, you’ll be swarmed with errors.
The ultimate delink is copy picture.
Maybe someone stumbles over this post –
even if its way in the past…
I’ve got a problem:
I need to replace the current year in formulas of many sheets, those formulas are right next to the limit of 1024, as the year is contained in some network-file-name from which to include data;
So i went about trying to cope with those long strings –
as an automatic replace won’t work (“Formula too long”).
In test i’m able to write a formula which is exactly 1024 characters long into a Cell (from VBA),
BUT – herein lies my real problem – i cannot read it in again from VBA;
e.g.
Range(“B5?).Formula = str ‘works, with str containin 1024 long formula
str2 = Range(“B5?).Formula ‘fails with 1004 – application or objectdefined error
Anyone got an idea for me?
-Jonathan
BTW: reading the formula stops working when setting a formula of length greater than 1019 – 1020 cannot be read….
I am receiving the Excel message “Formula is too long” – is this formula limitation documented anywhere? How did you come to find that to be the limitation?
Dick,
I hit the same problem with array formulae, and have just tried the “replace” trick. It doesn’t work for me, though (on Office Excel 2003). To test it, I put into the range a very simple formula that is just “X_X_X()”, and then replace that with a long formula that I’ve built in a loop, in variable s. But the replace fails with a “type mismatch” error when s becomes 256 or so long. Here’s the code. Any solutions would be very welcome.
Sub test()
Dim s As String
Dim r As Range
s = “0?
For i = 1 To 88
s = s & “+” & i
Next i
MsgBox Len(s)
Set r = Range(“a1:a10?)
r.FormulaArray = “=X_X_X()”
r.Replace “X_X_X()”, s
End Sub
Jocelyn,
this works
Sub test()
Dim s As String
Dim r As Range
s = “0?
For i = 1 To 86
s = s & “+” & i
Next i
MsgBox Len(s)
Set r = Range(“a1:a10?)
r.FormulaArray = “=fred1+fred2?
r.Replace “fred1?, s
r.Replace “fred2?, s
End Sub
Another useful trick is to rename all the worksheets to very short names before inserting the array formulae, then rename them back afterwards.
You should also be aware that the 256 character transfer limits are not the same in different versions of excel.
Using XCL 2003 VBA, I create strings of dates that are pipe-delimited and paste each of them into one cell (my application requires this). The date format is mm/dd/yyyy. Since the number of dates is a variable and it may be up to 30 or 35 dates, my strings that exceed 255 characters are being truncated. Is there hope for pasting all of my complete strings in my worksheet?
Thanks for a response.
Chuck,
As long as you use the Value property to write the data to the cells, you should be fine and able to write up to 32,767 charaters worth of dates into a cell.
Please help. I’ve tried the same solution but it did not work for me. The X_X_X()) never get replaced. Is there any other way for me to do this.
Thanks
‘Update for sequence array: T120-T136
theFormulaPart1 = “=IF(ISERROR(INDEX(SGData!$A$1:$L$” & SGDataLastRow _
& “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
& “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
& “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)),””””,X_X_X())”
theFormulaPart2 = “INDEX(SGData!$A$1:$L$” & SGDataLastRow _
& “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
& “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
& “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)”
FormulaString = theFormulaPart1 + theFormulaPart2
With ActiveSheet.Range(“T120:T136?)
.FormulaArray = theFormulaPart1
.Replace “X_X_X())”, theFormulaPart2
End With
I think you must change:
.Replace “X_X_X())”, theFormulaPart2
To
.Replace “X_X_X())”, FormulaString
I get the same error but my ArrayFormula is only 121 characters long.
My formula is:
=SUM(IF(FREQUENCY(IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),””), IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),””))>0,1))
It finds the total no of unique values in a range of duplicating values.
I have been trying to spread the formula below in 2 parts. I have not been able to spread it !!
“=SUM(IF(‘Jalons et Niv Serv’!$F$1:$F$1000 = $G” & i & “,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 “”””,IF(‘Jalons et Niv Serv’!$U$1:$U$1000 = “”X”” ,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$7,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$8,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$9,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$11,1*(‘Jalons et Niv Serv’!$Q$1:$Q$1000),0),0),0),0),0),0),0))”
Could someone please help me.
Thanks a lot
Hi Dick,
I was wondering if you tried changing ‘Regional and Language Options’->’Standards and Formats’ on your windows installation to let say German (Switzerland).
In my case this resulted in ‘funny’ situation when calls to Replace function were just ignored.
After doing some debugging I’ve come up with rather obscure solution:
With ActiveSheet.Range(“E2:K7?)
.FormulaArray = theFormulaPart1
if .FormulaLocal.IndexOf(“;”) > 0 then
theFormulaPart2 = theFormulaPart2.Replace(“,”,”;”)
end if
.Replace “X_X_X())”, theFormulaPart2
.NumberFormat = “mmm dd”
End With
So, when using Replace function the 2 other formula parts must be changed from commas (,) to semicolons (;) before being passed to Replace function.
If comma=>semicolon transformation wouldn’t be applied, the Replace function would ignore both operations as they would violate the FormulaArray.
The code is far from being perfect, but it works at least for the SWISS ;)
If you build the partial formulas by using FormulaLocal (which is Localised) iso FormulaArray (which is US) does it work as expected?
Hmm, if in first place I would assign part 1 of the formula array to FormulaLocal, and execute Replace function i’m not getting FormulaArray at the end.
It seems that Range.Replace function modifies FormulaLocal content rather than FormulaArray.
Correct, the replace method of a range object works on the Localised formulas. Hence my remark.
Here is what I’ve found:
When you try to set the formula (the formulaArray, formula, formulalocal, etc.) via any method, the formula you use must be parsable. It does not need to value, it just needs to be syntactically correct so that Excel can parse it. Then, it seems, each part of the parse tree must be less than 256 characters. Thus, any quoted strings or token names in an expression must be less than 256 characters, though the whole expression can be more than 256 characters. If you try rng.replace(token,replacementString), the replacement string must be less than 256 characters. Repeated application of rng.replace can be used to build up a large formula, but each replacement must have a replacement string of less than 256 characters. Furthermore, the limit of 255 characters on tokens in the parsed formula prevent you from using this strategy:
rng.arrayFormula = “=””Function(param1,param2,zzzzz”””
rng.replace “zzzzz”, “param3,param4,zzzzz”
rng.replace “zzzzz”, “param5,param6,zzzzz”
…
rng.replace “zzzzz”, “paramN,paramM)”
rng.replace “”””, “”
This would work beautifully (and be a general, generic work-around, so long as the formula has no quotes in it), except that as soon as the string in the formula gets to be >255 characters, the replace function ceases working. One can do this:
rng.arrayFormula = “=””Function(param1,param2,zzzzz”””
rng.replace “zzzzz”””, “””&””param3,param4,zzzzz”
rng.replace “zzzzz”””, “””&””param5,param6,zzzzz”
…
rng.replace “zzzzz”””, “””&””paramN,paramM)”
rng.replace “””&”””, “”
rng.replace “”””, “”
but the second-last replacement fails. If I could think of a single replacement command that would strip out the ‘”&”‘ and the leading ” and trailing ” all in one replacement, then this would work as a general solution.
What if we needed to to the long formula trick with FormulaArray in R1C1 style? I find that the Replace part is simply ignored…
Is Excel set to R1C1 mode when you do the replace trick?
I know SendKeys is deprecated, but this also works:
Dim theFormula As String
theFormula = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
“MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
“DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
With ActiveSheet.Range(“A1:G5”)
.NumberFormat = “@”
.Value = theFormula
.NumberFormat = “mmm dd”
.Select
DoEvents
Application.SendKeys “{F2}^+~”
End With
End Sub
But you can’t step through it unless you want to end up in the Object Browser …
Sorry for lousing up the closing code tag!
shg: Fixed.
Another workaround to this is to define a name first and then use the name. It works, just tested it.
Here is the code… strLongFormula contains the text of your long formula.
objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
.FormulaArray = “=LongFormula”
End With
Here is the code… strLongFormula contains the text of your long formula.
objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
.FormulaArray = “=LongFormula”
End With
sounds great, however I couldn’t get it to work in 2007. VBA doesn’t seem to appreciate objWorkbook,objDataSheet, etc. and no idea how to get it to work with these. I’ve tried using workbook / worksheet objects but it doesn’t like those either. any idea what I’m doing wrong?
OK I declared the objects and used Set to assign them as needed in my workbook, however during this statement,
I still get: Run Time Error ‘1004’ – Application-defined or object-defined error
Brian,
You will get ‘Runtime Error 1004’ when there is a problem with the formula itself. When your code paused, go to the VB debug window and type:
? strLongFormula
Hit enter and copy the result to the clipboard. Try to enter that formula manually into Excel using the Formulas>Name Manager tool.
Thanks for the feedback. Didn’t find what that error all about, it goes right through the code with no error when I test it now. It writes the formula string in the Name “refers to” field, which seems correct.
The only hangup is when I write to the cell with the last statement, what I get is {=LongFormula} in the cell, instead of the formula itself.
Ahh I think I see now, your algorithm stores the formula in the name permanently, whereas here I was thinking it was trying to writing it back to the cell.
Brian, how did you make the replacement happen? It isn’t working for me.
Could you please post a simple example?
I’m not entirely sure why, but in implementation I had to add LookAt:=xlPart to my Replace implementation.
This is not necessary to get the example to work! — but, it fixed my specific implementation.
I made my Replace look like this:
Hi everybody,
I am trying to use the same solution that Dave gave but it is not working for me. The Replace does not do anything. This is the code:
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 = “=SUM(IF(FREQUENCY(IF((R4C1:R2000C1”“”“)*(R4C2:R2000C2=R10C2)*(R4C4:R2000C4=R7C2)*” & “X_X_X()” & “,MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
theFormulaPart2 = “(IF(R3C4=”“”“,1,R4C6:R2000C6=R3C4))*(IF(R3C6=”“”“,1,R4C10:R2000C10=R3C6))*(IF(R3C9=”“”“,1,R4C5:R2000C5=R3C9))*(IF(R3C12=”“”“,1,R4C13:R2000C13=R3C12)),MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
With ActiveSheet.Range(“E2:E2”)
.FormulaArray = theFormulaPart1
.Replace “X_X_X()”, theFormulaPart2
End With
End Sub
I appreciate all your help to resolve this issue.
Thanks a lot in advance.
Walter.
Walter: If I try to do it manually (after converting to A1 style reference) it errors because there are mismatched parentheses. I think that may be the problem, but I’m not sure. Can you record the formula manually and paste it here? Use code tags so you don’t lose the greater-than signs.
Thanks you Dick for your help, I just saw the error.
For fun, I wrote function to easily use the alternative Named Formula method to insert a long array formula. It’s convenient (i.e. no need to break apart the formula), but at the end of the day I don’t like this method because the formula does never appears in the formula bar, only the name.
Dim strName As String
‘Create a “unique” name
strName = “LongFormula” & (10 * Rnd + 1) * Format(Now(), “yyyymmddHhNnSs”)
objRng.Worksheet.Names.Add strName, strFormula ‘assumes formula already begins with =
objRng.FormulaArray = “=” & strName
End Sub
PLs help me on this…
This is my recorded code for Array formula..
I could not able run the same because of the lenght of the formula.
Range(“b14”).Select
Selection.FormulaArray = _
“=SUMIFS(All!C[6],All!C2,SUBSTITUTE(R4C2,”” “”,””””),All!C8,SUBSTITUTE(RC2,”” “”,””””))+(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”” “”,””””),Data!C8,SUBSTITUTE(RC2,”” “”,””””)))-SUM(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”” “”,””””),Data!C8,SUBSTITUTE(RC2,”” “”,””””),Data!C1,INDEX(OFFSET(All!R3C109,,,COUNTA(All!C109)),)))”
I have seen your slution for the same kind but i did get this as i’m very new for the VBA…
I strongly believe you can help on this.
Hello,
this is workaround is like magic.
Thank you very much.
I concur with felix. An excellent and very useful article and extremely comprehensive set of comments too. Well done all round!
I found this article very helpful, thanks to the original poster, and all the commenters.
The long array formulas that I’m trying to get into the cells are dynamically generated, and there’s not a natural split point where both sides are parsable. What I was able to exploit however was that my formulas had lots of long string constants in them. I was able to replace these with short placeholders, the expand the long strings back out.
For example, if the formula was something like
=someSub(“longstring”, “anotherlongstring”, “morelongstring”)
this became
=someSub(“$0”, “$1”, “$2”)
which is < 255 chars so it can be set, then finally expand the strings again using the Replace technique shown in the article.
My code (just drafted so can't say it's perfect)
Public Sub setFormulaArray(cellRange As Range, formula As String)
' For short formulas, just do it the simple way
If Len(formula) <= 255 Then
cellRange.FormulaArray = formula
Return
End If
' Create the replacement formula, replacing strings as "$1" placeholders
Dim replacementArray(25) As String
replacementArraySize = 0
shortFormula = ""
insideQuote = False
For i = 1 To Len(formula)
char = Mid(formula, i, 1)
If (char = """") Then
If (insideQuote) Then
insideQuote = False
replacementArraySize = replacementArraySize + 1
Else
insideQuote = True
shortFormula = shortFormula & """$" & replacementArraySize & """"
End If
Else
If (insideQuote) Then
replacementArray(replacementArraySize) = replacementArray(replacementArraySize) & char
Else
shortFormula = shortFormula & char
End If
End If
Next i
' Set the cell's formula to the shortened version, which should now be < 255 chars
cellRange.FormulaArray = shortFormula
' Expand the created placeholders back out
For i = 0 To replacementArraySize – 1
cellRange.Replace "$" & i, replacementArray(i)
Next i
End Sub
For those situations where you can have a hidden sheet, you can use a copy and paste/pastespecial to get the formula into the cell.
I wasn’t able to get the above code options to work but did manage to get the copy/paste option going.
As Shg suggests above, SendKeys seems to work and was the least painful approach for me. In C#:
if (formula.Length < 255)
{
range.FormulaArray = formula;
}
else
{
range.Formula = formula;
range.Select();
Globals.ThisAddIn.Application.SendKeys("{F2}");
Globals.ThisAddIn.Application.SendKeys("^+{ENTER}");
}
(Globals was created for me by Visual Studio when I created my add-in).
Does anyone know if this method of replacing the arrayformula also works on Mac Excel 2011?
I received an error 1004 (application-defined or object-defined error)
Hi, Could anyone please tell me why the following code doesn’t work and how I can fix it?
—–
Dim p_1, p_2 As StringFor y = 1 To 100
p_1 = "COVARIANCE.S(IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "
Sorry, I forgot to add “=” before “COVARIANCE.S” in the code and “Next y” at the end.
Hello,
I’ve hit a brick wall with this one, can anyone help me get the following code to work?
Sub LongArrayFormula1()
Dim frmla1 As String
Dim frmla2 As String
Dim frmla3 As String
‘fullfrmla = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,MATCH(“”~””&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&””””,0)),ROW(A2:A10000)-ROW(A2)+1),1))”
frmla1 = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,” & _
“X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))”
frmla2 = “MATCH(“”~””&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&””””,0)),” & _
“ROW(X)-ROW(X)+1),1))”
frmla3 = “ROW(A2:A10000)-ROW(A2)+1),1)”
With ActiveSheet.Range(“AF2”)
.FormulaArray = frmla1
.Replace What:=”X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla2″, LookAt:=xlPart
.Replace What:=”ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla3″, LookAt:=xlPart
End With
End Sub
At the moment it is just displaying the following in cell AF2:
=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))
No error, but the replace doesn’t appear to be working.
I have data in three columns (A,B, and C) and I want the cell AF2 to display the number of unique values when those three cells are concatenated. (A10000 is only used to make sure that all cells are included, as the populated rows will change).
Thanks.
Chris: I changed it around a bit, but I can’t get it to work either. Actually, if I step through the code it works. If I just run the code, it doesn’t. I tried putting some .Calculate calls in there as well as some Application.Wait calls to see if I could get it to go, but no dice.
Sub LongArrayFormula1()Dim frmla1 As String
Dim frmla2 As String
Dim frmla3 As String
'fullfrmla = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)),ROW(A2:A10000)-ROW(A2)+1),1))"
frmla1 = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",YYYY),ZZZZ),1))"
frmla2 = "MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)"
frmla3 = "ROW(A2:A10000)-ROW(A2)+1"
With ActiveSheet.Range("AF2")
.FormulaArray = frmla1
.Replace What:="YYYY", Replacement:=frmla2, LookAt:=xlPart
.Replace What:="ZZZZ", Replacement:=frmla3, LookAt:=xlPart
End With
End Sub
formulaArray, call my attention I didn’t found anything about frequency function as formula array in a macro.
my concern is I would like to know how to loop the frequency function.
Range(“Q2:Q54”).Select
Selection.FormulaArray = “=FREQUENCY(R[1]C[-15]:R[18]C[-10],RC[-1]:R[52]C[-1])”
Range(“R2:R54”).Select
Selection.FormulaArray = “=FREQUENCY(R[2]C[-16]:R[19]C[-11],RC[-2]:R[52]C[-2])”
from here how can I apply the for .. next loop ideas. thanks.
In .Replace “X_X_X())”, theFormulaPart2
What if size of theFormulaPart2 is greater than 255 chars, I am getting a type mismatch error if theFormulaPart2 is greater than 255 chars
I worked on this issue for a few hours and was able to make my array formula with the replace function to work properly. I realized that each of the parsed formula should be executable with the correct syntax. You cannot subdivide your long formula to just random divisions. Not sure that I am clear on this comment. Here’s my code that works:
formula3 = "=IF(B5=""Armouring"","""",Y5*COUNT(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5=0),(E5:K5)))+" & _"dummy1())"
formula4 = "IF(Y5=""12"",SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<12)*(E5:K5>0),Y5-(E5:K5))),dummy2())-W5"
formula5 = "SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<8)*(E5:K5>0),Y5-(E5:K5))))"
range("R5").Select
With ActiveSheet.range("R5") 'ABS Formula
.FormulaArray = formula3
.Replace "dummy1()", formula4, LookAt:=xlPart
.Replace "dummy2())", formula5, LookAt:=xlPart
End With
I’ve ran across this very frequently when automating report generation. One of the things I have tried to do to fix this is the following:
http://www.abstraction-project.com/documents/excel/FunctionCalls.xlsm
The above link represents a document which constructs the formula in objects by using method calls. Knowing the formula length issue exists, it uses placeholders for each parameter and component of the expression. Binary operands, function parameters, and so on. I’ve only had a chance to test it on Excel 2013.
Hope it helps someone else, too!
Originally created it as an answer to a question: http://stackoverflow.com/questions/2320258/is-there-a-workaround-when-hitting-the-maximum-summands-in-sum-in-an-excel-form/25945343#25945343
Where I go as ‘Alexander Morou’
Guys, I have used below function and it works perfect, however this takes a while to take effect(because of ‘For’ loop)
Function CreateArrayFunction(ByVal sRange As String, formula As String) As Boolean
For Each oCell In Worksheets(“PCN IT Data Tables”).Range(sRange)
oCell.FormulaArray = formula
Next oCell
End Function
Any one knows alternative to this?
Thanks,
-N
Guys, Ihave below function and it works perfect, however it takes a while to take effect(because of for loop)
Function CreateArrayFunction(ByVal sRange As String, formula As String) As Boolean
For Each oCell In Worksheets("PCN IT Data Tables").Range(sRange)
oCell.FormulaArray = formula
Next oCell
End Function
does anyone knows alternative to this?
Thanks
-N
Ok, so 255 character limit is applicable to function posted by me above. I have tried splitting formulas. It gives me error
“Application-defined or object-defined error”
any clue on this guys?
here is my formula:
“=IF(ISERROR(MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13)))))),0,MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13))))))”
Hi guys. I have read every single comment in this thread and looked in other websites but I haven’t been able to solve this problem.
I am doing a macro that uses array formulas and when the macro hits the first array formula, I get an error. Basically I was getting the 1044 error referencing to the 255 character limit on array formulas. So I tried splitting the formula in 2 parts each with less than 255 characters (about 180 each) and still hitting an error. My original formula that you would see in an Excel cell is as follows:
Obviously too long. So following this post suggestion, I broke down the code in the following manner:
When I get the error and debug, it always highlights the “.FormulaArray= FormulaPart1” line.
Any idea how I can fix this problem?? I would greatly appreciate it! I’ve been stuck with this for a whole day!!
Thank you in advance.
I apologize, the error I’m getting is the “Run-time error ‘1004’: Unable to set the FormulaArray property of the Range class”
Just to make it easier to see, I’m breaking up the formula in the “if false” part of the IF function.
Help please!
Max: FormulaPart1 has to be a valid formula or you’ll get that error. Here’s how you fix it.
When you get that error, type ?FormulaPart1 in the Immediate Window. In Excel, go to File – Options – Formulas and change to R1C1. Go to B12 and paste the text from the Immediate Window into the cell and try to enter the formula. It will tell you it’s not valid and tell you that the part that says
is the culprit.
After you add the missing closing quotes, you try it again and it says
is now the problem. So you get rid the of the colon, I guess, and it works. Make those same changes in your code and it will run. Then switch back to A1 mode.
Thank you Dick! I was able to get past this part of the forumla and I am no longer getting an error here. However I’m having a different issue in this code.
So after making the right corrections, the code runs without errors and substitutes the “XXX()” how it is supposed to. However, since in FormulaPart1, this last part of the IF function we are writing it “”XXX()””)” which is fine because that way excel detects the XXX() as text and replaces it for FormulaPart2. However, when this replacement gets done, FormulaPart2 comes in surrounded by quotation marks and therefore the formula is not getting executed. I copy the code as I have it now and what the output is once I run the macro:
Dim FormulaPart1 As String
Dim FormulaPart2 As String
FormulaPart1 = “=IF(ISERROR(INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4)),””””,””XXX()””)”
FormulaPart2 = “INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”
With ActiveSheet.Range(“B12”)
.FormulaArray = FormulaPart1
.Replace “XXX()”, FormulaPart2
End With
The outputof this code is:
{=IF(ISERROR(INDEX(‘Acquisition Branch’!$A$1:$J$242,SMALL(IF(‘Acquisition Branch’!$C$2:$C$242=’Branch CAP Tracking’!$C$1,ROW(‘Acquisition Branch’!$C$2:$C$242)),ROW(1:1)),4)),””,”INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”)}
Note how FormulaPart2 comes in FormulaPart1 in between quotation marks > “INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”
What this is causing as well is that I end up with the first part of the formula in A1 style while the second part remains in R1C1 style (I’m assuming this is because FormulaPart2 is coming in as text because of the quotation marks, instead of being the IF False clause of the function)
If I write …,”XXX()”)” in FormulaPart1 instead of …,””XXX()””)”, I get a syntax error, so I tried that.
If someone knows a way around this I would greatly appreciate the help! It might just be a syntax thing, but I couldn’t figure it out.
Thank you so much in advance!
Max
Thank you Dick, but no luck… When writing the code like that it doesn’t substitute the “XXX()”
This is how it looks like now.
Dim FormulaPart1 As StringDim FormulaPart2 As String
FormulaPart1 = "=IF(ISERROR(INDEX('Acquisition Branch'!R1C1:R242C10,SMALL(IF('Acquisition Branch'!R2C3:R242C3='Branch CAP Tracking'!R1C3,ROW('Acquisition Branch'!R2C3:R242C3)),ROW(R[-11])),4)),"""",""XXX()"")"
FormulaPart2 = "INDEX('Acquisition Branch'!R1C1:R242C10,SMALL(IF('Acquisition Branch'!R2C3:R242C3='Branch CAP Tracking'!R1C3,ROW('Acquisition Branch'!R2C3:R242C3)),ROW(R[-11])),4)"
With ActiveSheet.Range("B12")
.FormulaArray = FormulaPart1
.Replace """XXX()""", FormulaPart2
End With
And the formula output when I run the macro looks like this:
Any other ideas?
Nevermind! Changed the cell reference style back to R1C1 style and it works now! Thank you!
Is there a way to change all of the code I have to R1C1 style not manually? I have part of the code in A1 style and part of it in R1C1 style. My macro is pretty complex and I want to avoid making mistakes.
No. All of my attempts to reliably convert code end in failure. All I can tell you is: before you change anything, write a test. Then make the change and see if the test still works. That’s not a quick solution, but as far as I know, there isn’t a quick one.
Hi All,
I have a problem. When i record my working formula in macro, the following codes appears.
But when i run it, i encounter 1004 error.
Would you know if this has sometgint to do with the length of the codes? Thank you in advance!
Hello!
I am trying the way Nick and Brian were talking about (defining a name first and referring to that).
My code is as follows:
Dim strLongFormula As String
strLongFormula = “MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “))+((MAX(INDIRECT(“”‘Follow Up Appointments’!B””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)&””:G””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)))))”
ThisWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
wsTimeline.Activate
With wsTimeline.Range(“TIMELINE_FOLLOWUP”)(lastrow + 1)
.FormulaArray = “=LongFormula”
End With
The code runs, and the formula appears in the ‘Refers To’ section of the named range.
The formula in the string also works, I have copied it from the name manager and replaced the cell and it’s all fine.
However my formula in the required cell is just {=LongFormula}.
What am I doing wrong?! Please help, I am rather confused!
@Emily_jane: It won’t work with array formulas. I know Nick said he tested it, but I suspect it was with a formula that worked as a non-array formula. I can’t get it to work with a formula that truly need to be evaluated as an array.
Cheers Dick, I’ll try the original way you suggested. Thanks for starting this thread by the way, very useful!
Hello,
I think that I have a similar problem as Max.
My original formula that you would see in an Excel cell is as follows:
My VBA-Code looks like this:
My result looks like this:
If I try to change it to R1C1 style the outcome doesnt change.
Thanks in advance.
I recognized that I had a mistake with a wrong semicolon. My result looks like this now:
{=VERKETTEN(INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));1))&","&INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));2))&","&"INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),3))&";"&INDEX(INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),4))")} Dont get confused by the formula terms. Its the german translation. But I think thats the reason why it isnt working correctly. It has to be correlated with the use of signs.Sorry my fault:
Hi,
I am trying to split the below formula into 2 segments to cure my problem but I still receive the run-time error:
I have tried just entering the following which is under 255 characters but I still receive an error. Does anyone have any ideas??
OK, got the first part of the formula working! However I am now having real issues splitting my formula up. Any ideas?
Hi, we have a situation where we need split the long array formula Dynamically through vba code. we store the formulas(as string) in a master worksheet. we then loop the rows and set the actual formulas in other worksheets. in this case, with long formula arrays, we have to split the formulas dynamically and replace it through code. all parts is working other than “splitting the formula”. we cant split on every 200 characters as the formula has to be valid. we are struggling here to find how to split a formula which should also be valid.
any help will be greatly appreciated. Thanks.
Maha: You could tokenize the formula like here so that you only split where the formula will still work on its own. But that seems like a lot of work. It’s best if a human splits the formula. You say the formulas are listed in a spreadsheet. Can you split it as listed in the spreadsheet and simply combine the components in the code?
Hi Dick, Thanks for the suggestion. after days of struggle and googling we decided to split manually.
Thanks a lot. That is the decision!
Hello! My formula is the following:
Entire_Formula = “=IF(RC[-3]””261””,IFERROR(IF(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),33))=””v””,IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””),IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””)/RC[2]),””””),IFERROR(IF(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),33))=””v””,IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””), IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””)/RC[2]),””””)- IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]= DIESEL!R3C30:R100C30),ROW(DIESEL!R3C30:R100C30)),32,1,1,””DIESEL””)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””))”
How would you split it to make it works in a formulaarray statement?
I found my mistake! It was a missing quotes. Thanks!
Hello Dick
I have an array formula in VBA which successfully returns the desired result into the spreadsheet cell without any run-time errors or similar, but when I go into that cell, the whole function with all its replacements mandatorily defined in the VBA code is not there. What is there is just ={IF(ITOTDR1!I22=””,theFormulaPart1,ITOTDR1!I22)}
Otherwise it works as far as the final result, but another strange thing is that if I manually go into that cell and then go out without confirming the curly brackets by Ctrl+Shift+Enter, the result is the same as with the curly brackets – array formula – where I would have thought that due to the nature of this function it must always be an array formula otherwise the result will not be correct.
This is the code:
Sub COMBINETOOL_Rectangle4_Click()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPartsub_a As String
Dim theFormulaPartsub_b As String
Dim theFormulaPartsub_c As String
Dim theFormulaPartsub_d As String
Dim theFormulaPartsub_e As String
End Sub
Is it normal or can the code be easily adjusted somehow for the full nested array function to appear in the spreadsheet cell?
Thank you really a lot,
jiri
Jiri
Change your Replacement arguments to exclude the double quotes. ex:
Dick – thanks but there`s still apparently something wrong, what I am now getting is #NAME? error.
Do you think I could send you the spreadsheet with the macro? I have cut all the other stuff and left just the problematic part with the formula so that visibility of what I need to get fixed should be clear. thankyou a lot, jiri
Sure, send it over. dick@kusleika.com
great, I have just done so and hope the e-mail has reached you. Thanks for your kind assistance!
@jiri
It turns out that you can’t use R1C1 in a replace unless you’re using R1C1 in Excel. When I changed your replacement text to A1 notation, it worked without error. I rewrote it a little
Hi,
I am also stuck, using arrayformulas with vba. This is a part of my code:
This code results in the run-time error ‘1004’: Unable to set the FormulaArray property of the Range class.
When I check the content of the ArrFormula variable it is:
When I copy-paste this manually in the worksheet (with ++ it works just fine.
Can someone point me in the right direction?
Thanks very much in advance.
Regards,
Gerard
Gerard: You’re array formula is too long. You’re building up the whole string before you assign it to the FormulaArray property. You need to write a smaller array formula to the cell, then do a Find/Replace to make it bigger.
Hi,
I had the problem of too long (multi-cell) array formula (len of 308) via VBA.
I put the formula into a user-defined-name (with { and } ). It worked when entered in Excel as an array formula.
I used it in my VBA code ( .FormulaArray = “=myDefinedName.Long” ) and it worked as well.
But I can imagine that it won’t work with every array formula (and it doesn’t work as a list in data validation).
Regards,
Hasan
correction in the second sentence: withOUT { and }
Hi,
I’d like to know how to convert this excel array fomula into vba:
=SUM(IF($A$2:$A$10000=$H$2:$H$10000,1,0)*$K2) with CTRL+SHIFT+ENTER
THANKS IN ADVANCE
You don’t need an array formula for that
I have a long array formula that I am having issues breaking into parts. How would I do this?
=IF(MAX(IF(A3=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1),Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6)))=0,””,MAX(IF(A3=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1),Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6))))
Sorry, it is actually more like this because of the variable to handle the row…
=IF(MAX(IF(A” & varNextRow & “=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1),” & _
“Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6)))=0,” & Chr(34) & Chr(34) & _
“,MAX(IF(A” & varNextRow & “=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1)” & _
“,Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6))))”
If anyone ever stumbles onto this, here is something I created that really helped me with a long array. Breaking each index part into it’s own formula is key
https://www.mrexcel.com/board/threads/long-array-issue-with-vba.1059978/#post5104633