PDA

View Full Version : Solved: Extra Series ("File") in Chart with VBA Code



TimboTim
01-07-2013, 02:53 AM
Hey guys and girls,
I'm using the below code to generate a chart however this chart seems to generate a extra series. Here is what shows in the formula when selecting the series:
=SERIES(Menu!$B$5,Menu!$A$6:$A$17,Menu!$B$6:$B$17,1)

Here is my code I have created and using:
With Charts.Add ' Make new chart
' Set its properties
.ChartType = xlLine
.Location Where:=xlLocationAsNewSheet, Name:="C" & Right(ws3a, 4)
.Move After:=Sheets(ws3a)
With .SeriesCollection.NewSeries
.XValues = "(" & ws3a & "!$D$3," & ws3a & "!$J$3," & ws3a & "!$P$3," & ws3a & "!$V$3," & ws3a & "!$AB$3," & ws3a & "!$AH$3," & ws3a & "!$AN$3," & ws3a & "!$AT$3," & ws3a & "!$AZ$3," & ws3a & "!$BF$3," & ws3a & "!$BL$3," & ws3a & "!$BR$3)"
.Values = "(" & ws3a & "!$F$189," & ws3a & "!$L$189," & ws3a & "!$R$189," & ws3a & "!$X$189," & ws3a & "!$AD$189," & ws3a & "!$AJ$189," & ws3a & "!$AP$189," & ws3a & "!$AU$189," & ws3a & "!$AU$189," & ws3a & "!$AV$189," & ws3a & "!$BA$189," & ws3a & "!$BA$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BN$189," & ws3a & "!$BT$189)"
.Name = "Total Income"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$189," & ws3a & "!$M$189," & ws3a & "!$S$189," & ws3a & "!$Y$189," & ws3a & "!$AE$189," & ws3a & "!$AK$189," & ws3a & "!$AQ$189," & ws3a & "!$AW$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BB$189," & ws3a & "!$BC$189," & ws3a & "!$BH$189," & ws3a & "!$BI$189," & ws3a & "!$BO$189," & ws3a & "!$BU$189)"
.Name = "Total Cost"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$191," & ws3a & "!$M$191," & ws3a & "!$S$191," & ws3a & "!$Y$191," & ws3a & "!$AE$191," & ws3a & "!$AK$191," & ws3a & "!$AQ$191," & ws3a & "!$AW$191," & ws3a & "!$BC$191," & ws3a & "!$BI$191," & ws3a & "!$BO$191," & ws3a & "!$BU$191)"
.Name = "Overheads"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$F$191," & ws3a & "!$L$191," & ws3a & "!$R$191," & ws3a & "!$X$191," & ws3a & "!$AD$191," & ws3a & "!$AJ$191," & ws3a & "!$AP$191," & ws3a & "!$AV$191," & ws3a & "!$BB$191," & ws3a & "!$BH$191," & ws3a & "!$BN$191," & ws3a & "!$BT$191)"
.Name = "Annuity Income"
End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Sett ings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings! $C$3,Settings!$C$3)"
.Name = "=Settings!$B$3"

End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Sett ings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings! $C$4,Settings!$C$4)"
.Name = "=Settings!$B$4"
End With
End With

Cheers

TimboTom

p45cal
01-07-2013, 03:16 AM
Step throuigh the code with F8 and look at the chart after the line With charts.add is executed; is it an empty chart? Excel wants to be helpful sometimes by trying to work out what you want to chart. It does this froim the selected range at the time the chart is added. It may be you have to either:
1. make sure nothing (no data) is selected before that line is executed or,
2. as soon as the chart has been added, remove all series with the likes of
for i = 1 to .series.count
.series(1).delete
next i
(this is just air code)

I'm pretty sure there's also a way of adding a blank chart with another argument or two in the charts.add line.

TimboTim
01-07-2013, 03:48 AM
Thanks p45cal, I assumed it was something I was doing, i ran through the code and as soon as .add was called it was added at this point.

I Resorted to your suggestion of clearing all the series
(For future reference:

Dim n As Long
With ActiveChart
For n = .SeriesCollection.Count To 1 Step -1
.SeriesCollection(n).Delete
Next n
End With

does the trick.

Regards