Entering Long Array Formulas in VBA
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.