How to include a secondary y axis in a line chart vba?

[SBF12345]

I have the following code building charts, but am stumped as to how to successfully include a secondary y axis. the hasaxis properties don't seem to be including a secondary axis

With ch        .HasTitle = True
        .ChartTitle.Text = aa & StartDate & " to " & EndDate
        .ChartTitle.Font.Size = 8
        .ChartType = xlLine
        .SetSourceData Source:=Range(RngStart & ":" & RngEnd)
        .SeriesCollection.Add (Worksheets(DataSht).Range(DataRange))
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = True
        .HasLegend = False
       
    End With

 

[Kamolga]

With a combochart, you can assign series a style and an .axisgroup 1 or 2

    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$C$6:$E$11")
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(3).ChartType = xlLine
    ActiveChart.FullSeriesCollection(3).AxisGroup = 1
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked
    ActiveChart.FullSeriesCollection(3).AxisGroup = 2

 

[SBF12345]

hmmm...is shapes object similar to chartobject object? I currently have a structure like below:

how can I incorporate the Shapes object and the AddChart2 method? Also, I am using Excel 2003 and not seeing the AddChart2 method. Is it possible this method was added later?

Set sh = ActiveWorkbook.Worksheets("LowDistCharts")    Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
    Set ch = chrt.Chart
    
    With chrt
        .Height = 300
        .Width = 300
        .Top = 1 + ((aa - 4) * 200)
        .Left = 1
    End With
    
    With ch
        .HasTitle = True
        .ChartTitle.Text = aa & StartDate & " to " & EndDate
        .ChartTitle.Font.Size = 8
        .ChartType = xlLine
        .SetSourceData Source:=Range(RngStart & ":" & RngEnd)
        .SeriesCollection.Add (Worksheets(DataSht).Range(DataRange))
        '.HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = True
        .HasLegend = False
       
    End With

 

[Kamolga]

is shapes object similar to chartobject object?

Yes it is

Also, I am using Excel 2003 and not seeing the AddChart2 method. Is it possible this method was added later?

The method was indeed added with 2013

 

[SBF12345]

that explains it

 

[SBF12345]

.SeriesCollection(1).AxisGroup = 2

this line works with charts and is producing a second axis