Error while creating multi series chart in excel using VBA

0
votes

I want to plot multi-series chart in excel sheet using VBA.

I want to add these data in tow series. Series1-> A1:B2 and Series2->A3:B4 ( A on X-axis , B on Y-Axis) A B
1 Test1 10 2 Test2 20 3 Test11 30 4 Test22 40

I have added below code but facing "Invalid parameters" error

Dim ochart As Object, ochartObj As Object
Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=10, left:=325, Width:=600, Height:=300)
Set ochart = ochartObj.Chart
ochart.ChartType = xlXYScatter
ochart.SeriesCollection(1).XValues = Range("B1:B2")
ochart.SeriesCollection(1).Values = Range("A1:A2")
ochart.SeriesCollection(2).XValues = Range("B3:B4")
ochart.SeriesCollection(2).Values = Range("A3:A4")

Please help :) Thanks!

2
excelvbacharts
What exact line is the error on? Also could it be because left:=325 is in lowercase? – erazorv4
Actually, Make sure SeriesCollection 1 and 2 are filled with something, you get that error when you're trying to access something out of the range of the array. – erazorv4
@erazorv4 : I am getting error in ochart.SeriesCollection(1).XValues = Range("B1:B2") line. lowercase left:= 325 is not causing this error. – user11664493

2 Answers

0
votes
Dim ochart As Object, ochartObj As Object
Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=10, left:=325, Width:=600, Height:=300)
Set ochart = ochartObj.Chart
ochart.ChartType = xlXYScatter

ochart.SeriesCollection.NewSeries
ochart.SeriesCollection.NewSeries

ochart.SeriesCollection(1).XValues = Range("B1:B2")
ochart.SeriesCollection(1).Values = Range("A1:A2")
ochart.SeriesCollection(2).XValues = Range("B3:B4")
ochart.SeriesCollection(2).Values = Range("A3:A4")

But you really should:

Dim ochart As Excel.Chart, ochartObj As Excel.ChartObject
0
votes

You'll need to create a new series for each one before assigning them values...

Dim ochart As Object, ochartObj As Object

Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set ochart = ochartObj.Chart

ochart.ChartType = xlXYScatter

ochart.SeriesCollection.NewSeries
ochart.SeriesCollection(1).XValues = Range("B1:B2")
ochart.SeriesCollection(1).Values = Range("A1:A2")

ochart.SeriesCollection.NewSeries
ochart.SeriesCollection(2).XValues = Range("B3:B4")
ochart.SeriesCollection(2).Values = Range("A3:A4")

Also, I would suggest that you declare your objects as their specific types. It makes the code be a bit more efficient, and you'll have the benefit of the IntelliSense. Accordingly, your code could be re-written as follows...

Dim ochart As Chart, ochartObj As ChartObject, oSeries As Series

Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set ochart = ochartObj.Chart

With ochart
    .ChartType = xlXYScatter
    Set oSeries = .SeriesCollection.NewSeries
    With oSeries
        .XValues = Range("B1:B2")
        .Values = Range("A1:A2")
    End With
    Set oSeries = .SeriesCollection.NewSeries
    With oSeries
        .XValues = Range("B3:B4")
        .Values = Range("A3:A4")
    End With
End With

Hope this helps!