Limit on array as chart series? (Excel 2000)

Topic

New Reply

I found some useful code recently which claimed to make charts independent of their source data ranges by making the assignment ser.values = ser.values, where ser is a series object. The practical effect of this is that in the SERIES formula for a chart, the cell range (e.g. B2:BA2) is replaced by an array of values (e.g.{1,2,…,52}).

This has been failing on certain charts with error 1004 unable to assign to series property, and on revisiting this today it seems to me that the problem is with the length of the array.

I have 52 values in each data series. Where each data point is a 3-digit number, the assignment works. If each data point is a 4-digit number, the assignment fails with the error given. However, I can’t find a specific limitation of Excel that corresponds to this! (e.g. max formula length = 1024 characters, but I can’ t be exceeding 300 in total).

Can anyone shed any light on which limit I’m exceeding?

Thanks

Jeremy

Reply | Quote

Replies

I don’t know what your code is but would guess that you if you are not hitting the 1024 formula length limit, you may be hitting the old 255 character string length limit?

Reply | Quote

I experimented a bit, and I can confirm Rory’s idea. Apparently, the formulas for the values are stored as old-fashioned strings with a maximum length of 255 characters. The code fails as soon as the formula for one of the series exceeds 255 characters.

Reply | Quote

Thanks guys. I did some more testing and reached the same conclusion. Annoyingly, if I have a chart that already has an array of values as the source for one of the data series, if I click on the line and go into the formula bar I can edit the SERIES formula e.g. by adding 1 in front of all the numbers so that the resulting formula DOES exceed the 255 limit, but if I try to record this action the macro recorder doesn’t capture the changes, so I appear to be stuck!

Jeremy

Reply | Quote

That is correct – in the user interface, you are limited by the 1024 character formula limit. In code, you are stuck with the 255 character limit. You might be better off taking a picture of the charts if that’s an option.

Reply | Quote