Results 1 to 9 of 9
  1. #1
    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

    Set chart data programmatically (XP SP1)

    Hi,
    I am attempting to set the source data for a chart programmatically. In theory not a problem. However, the range that I am trying to assign to the Series is a named range consisting of three non-contiguous areas - in this case D35:AA35, D51:AA51 and D67:AA67 - and if I use something like:
    <pre>activesheet.chartobjects(1).chart.seriescolle ction.add source:=range("_12MLRs")
    </pre>

    I actually end up adding 3 series to the chart rather than 1 series. Is this a known issue? Can anyone tell me either what I'm doing wrong or how to work around this before I embark on a lengthy investigation?
    Thanks,
    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Set chart data programmatically (XP SP1)

    Try something like:

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection( _
    activechart.SeriesCollection.count).values = _ Application.WorksheetFunction. _
    substitute(names("_12MLRs").referstoR1C1,"=","=(") & ")"

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set chart data programmatically (XP SP1)

    Rory, Excel will fight you tooth-and-nail with non-continuous areas. If you record a macro, you will see something like this:
    <pre>Option Explicit
    Sub Macro1()
    Dim c As Chart
    Set c = ThisWorkbook.Charts.Add
    c.ChartType = xlColumnClustered
    c.SeriesCollection.NewSeries
    c.SeriesCollection(1).Values = _
    "=(Sheet1!R35C4:R35C27,Sheet1!R51C4:R51C27,Sheet1! R67C4:R67C27)"
    End Sub</pre>


    If you try to do anything "better", like use a named range, Excel will not like it because your non-continuous array is placed into a 3x24 array. If you really want to use a named range, then I believe that you will have to traverse the Areas collection of your Range object and place all of your points into an array. Let me know if you need to use a named range. I think that I have also have success using the Union of several non-continuous ranges. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    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: Set chart data programmatically (XP SP1)

    Sammy,
    Yes I've noticed! Seems stupid that something I can do so easily via the user-interface using named range takes a hugely convoluted approach to replicate in code, but I guess I ought to be used to that by now. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> I will be investigating this soon anyway (I can't resist but I just don't have the time right now) so I'll look into the Union function too. Of course, none of this would be necessary if copying the sheet left the chart intact rather than giving me data-arrays of {0,0,0,0,0,0,0...} <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  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: Set chart data programmatically (XP SP1)

    Steve,
    Much obliged! Seems like a lot of work to do something fairly simple but hey, it works and that's all I care about at this point! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Saves me one of my investigations that takes 2 days due to all the wild tangents I go off on, too.
    [Later edit]
    In point of fact, it seems (with limited testing) to work OK with just:
    <pre>With ActiveChart
    .SeriesCollection.NewSeries
    .SeriesCollection(.SeriesCollection.Count).Values = _
    Names("_12MLRs").RefersToR1C1
    End With
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set chart data programmatically (XP SP1)

    Steve, that was pure genius! I never even knew about the Names collection. However, even though it was slick, the Substitute boggled my mind, so I rewrote it:
    <pre>Option Explicit
    Sub Macro1()
    Dim c As Chart, s As String
    Set c = ThisWorkbook.Charts.Add
    c.ChartType = xlColumnClustered
    c.SeriesCollection.NewSeries
    s = Names("_12MLRs").RefersToR1C1
    s = Right(s, Len(s) - 1) ' Strip leading =
    s = "=(" & s & ")" ' Add =( ... )
    c.SeriesCollection(1).Values = s
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Set chart data programmatically (XP SP1)

    Rory,
    I'm glad it worked.
    I hadn't tried the "simpler" code. I didn't think that excel would add the parentheses itself. Usually it is picky about those little things, so I just assumed it needed them.

    Steve

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set chart data programmatically (XP SP1)

    > Usually it is picky
    That is an understatement! I didn't even try: glad Rory did!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Set chart data programmatically (XP SP1)

    Sam,
    Thanks for the praise.
    I doubt the substitute "boggled your mind" too much. You essentially did the exact same thing in your code:
    1) replace the "=" in the front with " =("
    2) add the closing parenthesis, ")"

    Steve

Posting Permissions

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