Set chart data programmatically (XP SP1)

Topic

New Reply

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:

activesheet.chartobjects(1).chart.seriescollection.add source:=range("_12MLRs")

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,

Reply | Quote

Replies

Try something like:

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

Steve

Reply | Quote

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! 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:

With ActiveChart
    .SeriesCollection.NewSeries
    .SeriesCollection(.SeriesCollection.Count).Values = _
        Names("_12MLRs").RefersToR1C1
End With

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

Rory, Excel will fight you tooth-and-nail with non-continuous areas. If you record a macro, you will see something like this:

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

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 3×24 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

Reply | Quote

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. 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…}

Reply | Quote

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

Reply | Quote

> Usually it is picky
That is an understatement! I didn’t even try: glad Rory did!

Reply | Quote