Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine range of chart cells (Excel 2000)

    I'd like to know if there is an easier way to determine the range (and hence the count)
    of cells for the x-axis and y-axis in a regular column, bar or line chart.
    I set myself a small chart (attached) based on data in cells C4 through D16.
    Interrogating the chart:<pre> Debug.Print cht.SeriesCollection(1).Formula
    Debug.Print cht.SeriesCollection(1).FormulaLocal
    Debug.Print cht.SeriesCollection(1).FormulaR1C1
    Debug.Print cht.SeriesCollection(1).FormulaR1C1Local</pre>

    reveals the data sreies as a set of comma-delimited range strings:<pre>=SERIES(Sheet1!$D$4,Sheet1!$C$5:$C$16 ,Sheet1!$D$5:$D$16,1)
    =SERIES(Sheet1!$D$4,Sheet1!$C$5:$C$16,Sheet1!$D$5: $D$16,1)
    =SERIES(Sheet1!R4C4,Sheet1!R5C3:R16C3,Sheet1!R5C4: R16C4,1)
    =SERIES(Sheet1!R4C4,Sheet1!R5C3:R16C3,Sheet1!R5C4: R16C4,1)</pre>

    I can parse these strings and use them:<pre>?range("Sheet1!$C$5:$C$16").Cells.Count
    12 </pre>

    but this seems rather clunky.
    I've been looking for something along the lines of (waves hands in air) activesheet.charts(1).dataseries(xlaxis).range with little success.
    My Excel2000 VBA help files don't mention SeriesCollection (as far as I can see).
    Attached Files Attached Files

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

    Re: Determine range of chart cells (Excel 2000)

    Although the Chart object has a SetSourceData method, there is - as far as I know - no direct counterpart to get the source range of a chart or chart series. But you can retrieve the number of data points as
    <code>
    UBound(cht.SeriesCollection(1).XValues)
    </code>
    or
    <code>
    UBound(cht.SeriesCollection(1).Values)
    </code>
    XValues and Values are 1-based arrays, so UBound returns the number of elements.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine range of chart cells (Excel 2000)

    Thanks, Hans for a prompt response.
    Sometimes I think I'm going blind when I can't see what I think ought to be there.

    >has a SetSourceData method
    I had found a reference to this during my search and was, again, puzzled that one can set something but not read it back. Aaaaargh!

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Determine range of chart cells (Excel 2000)

    Hans
    While developing a response for Chris the other day (which you made redundant), I am certain that when using the Locals window I found two arrays (Values, and XValues), within the cht object. In subsequent work, I have been unable to expose them. Can you advise me whether I am mistaken; or how to view them?

    T.I.A.
    Regards
    Don

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

    Re: Determine range of chart cells (Excel 2000)

    XValues and Values are properties of a Series of a chart, not of the chart itself.
    Attached Images Attached Images
    • File Type: png x.png (3.7 KB, 0 views)

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Determine range of chart cells (Excel 2000)

    Thank you for the clarification Hans.
    Regards
    Don

Posting Permissions

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