Consulting

Results 1 to 5 of 5

Thread: Searching for a Chart Series by Series Name

  1. #1

    Searching for a Chart Series by Series Name

    I have a macro that will search for a chart's line Series with the name "Today". It works OK if there are actual lines being plotted on the chart, but if the chart is empty, then it crashes. You might think of such a chart as a template that has up to 9 lines "defined" ... you'll see the definitions if you look at the Source Data dialog box. If I provide the data in the correct location, then lines WILL be plotted. But at the moment there is no data in the defined data locations.

    When it crashes I get "1004 ... Unable to get the name property of the Series class".

    Since I can see the Series names in the dialog box, I presumed that VBA can too, but apparently not. Here's the basic code I'm using:
    [vba]Function GetTodayLineNo() As Long

    Dim SeriesNo as Long, Temp as String

    For SeriesNo = 1 To ActiveChart.SeriesCollection.Count
    On Error GoTo Ignore
    Temp = ActiveChart.SeriesCollection(SeriesNo).Name '<--Crash here
    If Left(Temp, 5) = "Today" _
    Then
    GetTodayLineNo = SeriesNo
    GoTo Finish
    End If
    Ignore:
    Next SeriesNo
    GetTodayLineNo = 0
    Finish:

    End Function 'GetTodayLineNo' [/vba]

    An added annoyance is that the "On Error" statement is ineffective. Phffft! Any ideas??

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hey CD,

    Any chance of getting a sample sheet of what you're trying to do? I tried creating one with 9 series', created the chart, then cleared out one and it still worked (even when i commented out the on error statement).
    I then tried deleting a column completely, the series name said #REF!, and that was what populated the Temp variable then.
    I'm attaching mine in case I missed how you really set it up.

    Matt

  3. #3
    Hey, Matt, thanx for taking the time to review this. Sorry for my delay in getting back to you.

    I haven't had time to review your stuff, but I will tonight. I can't believe you didn't have any problems. I ran that sucker over and over again looking for the problem, and finally decided that I just couldn't do what I want to do. I guess I was hoping my (inelegant) code had a flaw. Pffft!

    One difference (which shouldn't matter) is that my data isn't on the same sheet as the chart. Yours is. I'm using embedded charts, but they are all on a single sheet that I use for charts. Data is several sheets away. It will be difficult to send anything, but I'll try doing it in a different workbook and check to see if having data on a different sheet matters. With my luck it will work just fine in a different workbook. I'll get back to you.
    Thanks again for your time.
    Sid

  4. #4
    Matt, your chart is nothing like mine. I'm attaching a VERY abbreviated example of mine with the macros.

  5. #5
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    341
    Location
    Hi,

    You will have trouble get information from a series that has blank data.
    In your case it is not the 'Today Line' that is empty but the RSI series. Your error checking in the GetTodayLineNo was such that if an error was encountered it stopped looking. I have modified so it checks all series and returns the correct value when it finds the right label.
    [vba]Function GetTodayLineNo() As Long '5/16/06
    'Chart must be preselected
    Dim SeriesNo&, Temp$
    Dim strTemp As String
    Application.ScreenUpdating = False
    On Error Resume Next
    For SeriesNo = 1 To ActiveChart.SeriesCollection.Count
    strTemp = ""
    strTemp = ActiveChart.SeriesCollection(SeriesNo).Name
    If strTemp = "Today Line" _
    Then
    GetTodayLineNo = SeriesNo
    GoTo Finish
    End If
    Next SeriesNo
    GetTodayLineNo = 0
    Finish:
    Application.ScreenUpdating = True
    End Function 'GetTodayLineNo'[/vba]
    Cheers
    Andy

Posting Permissions

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