VBA chart error Method 'Axes' of Object '_Chart' failed for secondary axis title

Question

I am trying to add a secondary vertical axis to my chart in VBA. I keep running into this error that says that the Method Axes of the Object _Chart has failed.

I have looked up solutions and implemented a few that stated to make sure that the secondary axis is first active. My code is below. The error occurs in the third to last line when I am trying to state that the secondary axis has a title. Any help is much appreciated since I am still a beginner in VBA.

Public Sub CreateChartForColumnsOneThreeandFive()

    Dim myChartColumnsOneThreeandFive As ChartObject

    Set myChartColumnsOneThreeandFive = ActiveSheet.ChartObjects.Add(Left:=150, Top:=150, Width:=500, Height:=400)

    myChartColumnsOneThreeandFive.Chart.HasTitle = True

    myChartColumnsOneThreeandFive.Chart.ChartTitle.Text = "Phase Detector Readback vs Substrate Forward Power"

    myChartColumnsOneThreeandFive.Chart.Type = xlLine

    myChartColumnsOneThreeandFive.Chart.SetSourceData Source:=ActiveWorkbook.Sheets("Sheet2").Range("C" & processRowBegin & ":C" & processRowEnd)

    myChartColumnsOneThreeandFive.Chart.SetSourceData Source:=ActiveWorkbook.Sheets("Sheet2").Range("E" & processRowBegin & ":E" & processRowEnd)

    myChartColumnsOneThreeandFive.Chart.SeriesCollection(1).Name = Range("C1")

    myChartColumnsOneThreeandFive.Chart.SeriesCollection(1).Name = Range("E1")

    myChartColumnsOneThreeandFive.Chart.SeriesCollection(1).Select

    myChartColumnsOneThreeandFive.Chart.SeriesCollection(1).AxisGroup = 2

    myChartColumnsOneThreeandFive.Chart.HasTitle = True

    myChartColumnsOneThreeandFive.Chart.ChartTitle.Text = "Substrate Forward Power vs Phase Detector Readback"

    myChartColumnsOneThreeandFive.Chart.Axes(xlCategory).HasTitle = True

    myChartColumnsOneThreeandFive.Chart.Axes(xlCategory).AxisTitle.Caption = "Time"

    myChartColumnsOneThreeandFive.Chart.Axes(xlValue, xlPrimary).HasTitle = True

    myChartColumnsOneThreeandFive.Chart.Axes(xlValue, xlPrimary).AxisTitle.Caption = "Substrate Forward Power"

      myChartColumnsOneThreeandFive.Chart.SeriesCollection(1).AxisGroup = xlSecondary

     myChartColumnsOneThreeandFive.Chart.HasAxis(xlValue, xlSecondary) = True

    myChartColumnsOneThreeandFive.Chart.Axes(xlValue, xlSecondary).HasTitle = True

    myChartColumnsOneThreeandFive.Chart.Axes(xlValue, xlSecondary).AxisTitle.Select

    myChartColumnsOneThreeandFive.Chart.Axes(xlValue, xlSecondary).AxisTitle.Text = _
        "Phase Detector Readback"

End Sub

at what line are you getting your error ? you are familiar how to use With ? your code is screaming for With myChartColumnsOneThreeandFive.Chart — Shai Rado
– Shai Rado, Commented Dec 9, 2016 at 7:29
@ShaiRado I am getting the error in line 21. It is where I am setting the Secondary axis title to true. I am not familiar with how to use With. Would that get rid of the error? — aquarules
– aquarules, Commented Dec 9, 2016 at 7:32
Ok thank you for the suggestion. I will look into it and start implementing it into my code. Are you familiar with this error message? — aquarules
– aquarules, Commented Dec 9, 2016 at 7:37
I think the your problem is you have only 1 Series here, and I think you intend to have 2, no ? One series looking on Column C, and another on Column E, No? That's why you are failing setting a secondary axis, you have only 1 Series — Shai Rado
– Shai Rado, Commented Dec 9, 2016 at 7:44

Shai Rado · Accepted Answer · 2016-12-09 07:56:39Z

1

Even though not in this code, I assume processRowBegin and processRowEnd are defined somewhere else as Long and they have a numeric value.

Try the code below, it runs without errors, I am not sure what is your final goal and how your chart suppose to look like, but I think you can modify it easily to fit your needs.

Option Explicit

Public Sub CreateChartForColumnsOneThreeandFive()

Dim myChartColumnsOneThreeandFive As ChartObject 

Set myChartColumnsOneThreeandFive = ActiveSheet.ChartObjects.Add(Left:=150, Top:=150, Width:=500, Height:=400)

With myChartColumnsOneThreeandFive.Chart
    .HasTitle = True
    .ChartTitle.Text = "Phase Detector Readback vs Substrate Forward Power"
    .Type = xlLine

    ' create series 1, set values and name
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = Range("C1")
    .SeriesCollection(1).Values = ActiveWorkbook.Sheets("Sheet2").Range("C" & processRowBegin & ":C" & processRowEnd)

   ' create series 2, set values and name
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = Range("E1")
    .SeriesCollection(2).Values = ActiveWorkbook.Sheets("Sheet2").Range("E" & processRowBegin & ":E" & processRowEnd)

    .SeriesCollection(1).AxisGroup = 2
    .HasTitle = True
    .ChartTitle.Text = "Substrate Forward Power vs Phase Detector Readback"

    ' set X-axis
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Caption = "Time"

    ' set Y-axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Caption = "Substrate Forward Power"

    .SeriesCollection(1).AxisGroup = xlSecondary

    ' add a secondary Y-axis ans set it
    .HasAxis(xlValue, xlSecondary) = True
    .Axes(xlValue, xlSecondary).HasTitle = True
    .Axes(xlValue, xlSecondary).AxisTitle.Select
    .Axes(xlValue, xlSecondary).AxisTitle.Text = "Phase Detector Readback"
End With

End Sub

answered Dec 9, 2016 at 7:56

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aquarules Over a year ago

I truly appreciate your input. It worked! I will definitely start implementing your suggestions and approach. It is much more simple and eloquent than the code which I wrote.

Shai Rado Over a year ago

@aquarules you're welcome, please mark as answer. do you know how ?

Patrick Mirek · Accepted Answer · 2020-09-16 16:03:42Z

0

I ran into this issue and solved it by changing the order of my code. Here is the order that worked:

Delete previous chart series (optional)
Set chart type
Add primary y-axis series
Add secondary y-axis series
Format secondary y-axis
Format x axis
Format primary y-axis

I think what solved it was setting the chart type right near the beginning. There's definetly some flexibility in the order I've written here, but I haven't explored it.

answered Sep 16, 2020 at 16:03

Patrick Mirek

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

VBA chart error Method 'Axes' of Object '_Chart' failed for secondary axis title

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related