Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel chart axes control in vba (english)

    I have a vba macro that reads up to 100 excel worksheets and plots specified ranges. In it, I can set axes ranges, titles, labels, etc. What I have not been able to figure out is how to set up a secondary axis from within vba without pointing to a particular series by number, and then forcing all subsequent plots to the secondary axis. If you record a macro when you add a series to a secondary axis, you get code that points to a series number. Can someone tell me how to set up lines of code to scale a secondary axis without pointing to a series number, and then how to force subsequent plots to fall onto the secondary axis?

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

    Re: excel chart axes control in vba (english)

    Welcome to Woody's Lounge!

    You can do something like this:

    Dim wsh As Worksheet
    Dim cht As Chart
    Dim ser As Series
    Set wsh = ActiveSheet
    ' Create blank chart
    Set cht = ActiveWorkbook.Charts.Add
    With cht
    ' Add first series
    .SetSourceData Source:=wsh.Range("A1:A3"), PlotBy:=xlColumns
    ' Specify secondary axis (you must already have at least one series)
    .HasAxis(xlValue, xlSecondary) = True
    ' Add another series
    .SeriesCollection.Add Source:=wsh.Range("B1:B3")
    ' Create reference to new series
    Set ser = .SeriesCollection(.SeriesCollection.Count)
    ' Tell Excel to plot series on secondary axis
    ser.AxisGroup = xlSecondary
    ' Place chart on worksheet
    .Location xlLocationAsObject, wsh.Name
    End With

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel chart axes control in vba (english)

    Thank you Hans. Can you guide me as to how to direct ranges to the plot inside a loop and point to the desired axis? I've taken your script and attempted to do that, but I'm getting an error. Below is what I've tried, and it shows you what I'm trying to do. I've put the add new series and plot new series lines inside a loop. If I loop from 1 to 1, it's identical to your script, but to repeat the plot causes an error "Unable to set the axis group property of the series class" on line "ser.AxisGroup = xlSecondary". Can we not point to the last series over and over again, each time placing it onto the secondary axis?

    Sub testplot2()

    Dim wsh As Worksheet
    Dim cht As Chart
    Dim ser As Series
    Dim sernum As Integer
    Dim rangename As String


    Set wsh = ActiveSheet
    ' Create blank chart
    Set cht = ActiveWorkbook.Charts.Add
    With cht

    ' Add first series on the primary axis
    .SetSourceData Source:=wsh.Range("A1:A3"), PlotBy:=xlColumns

    ' Specify a secondary axis (you must already have at least one series)
    .HasAxis(xlValue, xlSecondary) = True


    'Successively plot in a loop. A real script would point to a different
    'range each time, but if I can make this work, I can worry about that later.

    For sernum = 1 To 5

    ' Add a new series
    .SeriesCollection.Add Source:=wsh.Range("B1:B3")


    ' Create reference to the new series
    Set ser = .SeriesCollection(.SeriesCollection.Count)

    ' Tell Excel to plot the new series on the secondary axis
    ser.AxisGroup = xlSecondary

    Next




    ' Place chart on worksheet
    .Location xlLocationAsObject, wsh.Name
    End With

    End Sub

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

    Re: excel chart axes control in vba (english)

    Apparently, when you've set the second series to use the secondary axis, series added after that will automatically use the secondary axis, that's why the code fails. So it's sufficient to do this:

    If sernum = 1 Then
    ser.AxisGroup = xlSecondary
    End If

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel chart axes control in vba (english)

    Once again, thanks. I'm out of the woods. Below is a working sample now. Notice, though, that in the second loop, where I switch back to the primary axis, I have to issue ser.AxisGroup = xlPrimary every time through. If I if test it to =5, then it puts the 5th series on the primary axis and switches back to secondary. And without the if test, it works without error, unlike the first loop, and the plot is correct. I would have expected to need the if test in the second loop also. Any way, I am now where I need to be!!!


    Sub testplot2()

    Dim wsh As Worksheet
    Dim cht As Chart
    Dim ser As Series
    Dim sernum As Integer
    Dim rangename As String


    Set wsh = ActiveSheet
    ' Create blank chart
    Set cht = ActiveWorkbook.Charts.Add
    With cht

    ' Add series 1 on the primary axis
    .SetSourceData Source:=wsh.Range("A1:A3"), PlotBy:=xlColumns

    ' Specify a secondary axis (you must already have at least one series)
    .HasAxis(xlValue, xlSecondary) = True


    'Successively plot in a loop. A real script would point to a different
    'range each time, but if I can make this work, I can worry about that later.


    For sernum = 2 To 4

    ' Add a new series
    .SeriesCollection.Add Source:=wsh.Range("B1:B3")


    ' Create reference to the new series
    Set ser = .SeriesCollection(.SeriesCollection.Count)

    ' Tell Excel to plot the new series on the secondary axis

    If sernum = 2 Then
    ser.AxisGroup = xlSecondary
    End If


    Next

    For sernum = 5 To 9

    ' Add a new series
    .SeriesCollection.Add Source:=wsh.Range("c1:c3")


    ' Create reference to the new series
    Set ser = .SeriesCollection(.SeriesCollection.Count)

    ' Tell Excel to plot the new series on the secondary axis

    ser.AxisGroup = xlPrimary

    Next


    ' Place chart on worksheet
    .Location xlLocationAsObject, wsh.Name
    End With

    End Sub

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

    Re: excel chart axes control in vba (english)

    Glad it's solved. I can't pretend that I understand how it works exactly, programming chart creation is mostly trial and error for me.

Posting Permissions

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