CODEWITHSUNDEEP
excelvbadiagramming
MrPropper
MrPropper

Reputation: 3

Excel Diagramm Seriesnames in VBA

I'm programming a Makro for a Excel xy-Diagramm The diagramm is nearly correct, but i have dublicate DataSeriesNames; I already tried to go through all Series and Compare the Names. There were no Errors, but it didn't work.

Code was like that:

For a=1 to Count
  If ActiveChart.SeriesCollection(Count).Name = Range("A2").Value Then
    Name = true
  End If
  a = a + 1
Next

If Name = false Then
  ActiveChart.SeriesCollection.NewSeries
End If
ActiveChart.SeriesCollection(Count).Name = "='Tasks'!$D$" & i
ActiveChart.SeriesCollection(Count).XValues = "='Tasks'!$B$" & i
ActiveChart.SeriesCollection(Count).Values = "='Tasks'!$C$" & i

Mfg Robin

Upvotes: 0

Views: 760

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

There are a couple of things wrong here.

First of all, you're always looking at the same series! I think you want to replace Count with a in your If statement. 

If ActiveChart.SeriesCollection(a).Name

Secondly, once that is corrected, even if your Name variable gets set to True at some point, it may get reset to False later as the For...Next loop continues iterating through the remainder of the series collection. To prevent this, add an Exit For like this:

For a = 1 To Count
  If ActiveChart.SeriesCollection(a).Name = Range("A2").Value Then
    Name = True
    Exit For
  End If
  a = a + 1
Next

Also, I suspect you haven't declaring Name as a Boolean variable, so by default it's a Variant so its value isn't False to start with! You should declare the variable at the top of your procedure with Dim Name as Boolean, and then if you want Name to be False by default, you should really say so explicitly: Put Name = False before the loop. Moreover, Name is a terrible name for a variable... and so is Count Argh! I think your code should look like this:

Option Explicit

Sub MySub()
    Dim a As Long
    Dim NameExists As Boolean
    Dim SeriesCount As Long

    SeriesCount = ActiveChart.SeriesCollection.Count

    NameExists = False
    For a = 1 To SeriesCount 
      If ActiveChart.SeriesCollection(a).Name = Range("A2").Value Then
          NameExists = True
          Exit For
      End If
      a = a + 1
    Next

    If NameExists = False Then
    ' Rest of your code goes here...

End Sub

Upvotes: 1

Related Questions