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.
David Wasserman:
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
10 January 2005, 2:19 pmDick:
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.
11 January 2005, 8:06 amgokhan:
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?
23 March 2005, 10:01 amany comment, suggestion is highly appreciated.
jkpieterse:
I guess replacing the semicolons with comma's should help fix this.
24 March 2005, 2:22 amPaul:
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...
7 April 2005, 5:21 amJon Peltier:
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
8 April 2005, 7:53 amPaul:
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.
14 April 2005, 4:34 amJon Peltier:
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.
14 April 2005, 5:19 amPaul:
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.
14 April 2005, 8:52 amJon Peltier:
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.
15 April 2005, 5:45 amJonathan:
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?
13 December 2005, 5:56 am-Jonathan
Jonathan:
BTW: reading the formula stops working when setting a formula of length greater than 1019 - 1020 cannot be read....
13 December 2005, 6:00 amStephanie:
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?
16 February 2006, 1:49 pmJocelyn Paine:
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()"
19 March 2007, 5:06 amr.Replace "X_X_X()", s
End Sub
Charles Williams:
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.
20 March 2007, 3:48 amChuck Grob:
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.
23 August 2007, 3:57 pmjkpieterse:
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.
24 August 2007, 12:28 amHelen:
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")
30 January 2008, 9:54 am.FormulaArray = theFormulaPart1
.Replace "X_X_X())", theFormulaPart2
End With
jkpieterse:
I think you must change:
.Replace "X_X_X())", theFormulaPart2
To
.Replace "X_X_X())", FormulaString
31 January 2008, 5:31 amPiyush:
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.
22 February 2008, 8:09 amRUSSOOL:
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
7 May 2008, 1:50 pm