Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Limit on array as chart series? (Excel 2000)

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Limit on array as chart series? (Excel 2000)

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit on array as chart series? (Excel 2000)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Limit on array as chart series? (Excel 2000)

    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Limit on array as chart series? (Excel 2000)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •