• Set chart data programmatically (XP SP1)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Set chart data programmatically (XP SP1)

    Author
    Topic
    WSrory
    AskWoody Lounger
    September 9, 2002 at 10:49 am #376195

    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
    Viewing 1 reply thread
    Author
    Replies
    • WSsdckapr
      AskWoody Lounger
      September 9, 2002 at 12:11 pm #615181

      Try something like:

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

      Steve

      Reply | Quote
      • WSrory
        AskWoody Lounger
        September 9, 2002 at 12:29 pm #615184

        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! grin 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
      • WSSammyB
        AskWoody Lounger
        September 9, 2002 at 12:46 pm #615188

        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
        • WSsdckapr
          AskWoody Lounger
          September 9, 2002 at 3:17 pm #615239

          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
    • WSSammyB
      AskWoody Lounger
      September 9, 2002 at 12:13 pm #615182

      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
      • WSrory
        AskWoody Lounger
        September 9, 2002 at 12:24 pm #615186

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

        Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          September 9, 2002 at 12:47 pm #615190

          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
          • WSSammyB
            AskWoody Lounger
            September 9, 2002 at 1:08 pm #615198

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

            Reply | Quote
    Viewing 1 reply thread
    Reply To: Set chart data programmatically (XP SP1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.