CODEWITHSUNDEEP
vbaexcelchartsseries
Jean-François Corbett
Jean-François Corbett

Reputation: 38520

Add an empty series on an Excel chart

I'm trying to add many series to a chart using VBA, as in the code below. 

For i = 0 To 9
    Set serNew = chtMap.SeriesCollection.NewSeries
    With serNew
        .XValues = Range("Y4").Cells(1, 1 + 2 * i).Resize(32000, 1) 
        .Values = Range("Y4").Cells(1, 2 + 2 * i).Resize(32000, 1) 
    End With
Next i

The ranges for some of the series have no data in their cells yet; the user will write/load this data later. The idea is to have the chart ready for when they do.

Problem: when the loop hits such a yet empty range, I get an error 1004: Unable to set XValues property of the Series class. Why and is there a way around this?

The weird thing is that doing this manually in Chart menu --> |Source Data... works perfectly fine. Actually, if you record a macro while doing this manually, the result is as follows: 

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).XValues = "=Sheet2!R4C31:R32003C31"
ActiveChart.SeriesCollection(4).Values = "=Sheet2!R4C32:R32003C32"

but then Excel gives an error when re-playing this macro!

Doing this manually is not a pleasant prospect. I guess I could stick sham data in the cells, create the series, and then delete the sham data. Do I really have to pull such a bait and switch on Excel?

Upvotes: 1

Views: 4322

Answers (4)

John Alexiou
John Alexiou

Reputation: 29244

I suggest (as I have done myself) to hide some fake data somewhere and have all the series default to point there. Also hide the series from the plot with .LineStyle=xlNone and .MarkeStyle=xlNone and even ActiveChart.Legend.LegendEntries(i).Delete. Then when customer fills in the data, run a macro that replaces where the series points, and sets the line/marker styles, as well as ActiveChart.HasLegend = True to recreate the legend entries. If fact I had to switch the legend off and then on again for things to reset properly.

Good luck.

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

Old question but...

I just wrote this code and tested in Excel 2010 and 2016. It adds a new series to an existing XY Scatter chart, and the new series formula links to the blank ranges used for .Name, .Values, and .XValues:

Sub AddBlankSeries()
  Dim srs As Series

  Set srs = ActiveChart.SeriesCollection.NewSeries

  With srs
    .Name = "=" & ActiveSheet.Range("E2").Address(, , , True)
    .Values = ActiveSheet.Range("E3:E8")
    .XValues = ActiveSheet.Range("D3:D8")
  End With
End Sub

Then I went back to the original code:

Sub AddSeveralBlankSeries()
  Dim i As Long
  Dim chtMap As Chart
  Dim serNew As Series

  Set chtMap = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

  For i = 0 To 9
    Set serNew = chtMap.SeriesCollection.NewSeries
    With serNew
      .XValues = Range("Y4").Cells(1, 1 + 2 * i).Resize(32000, 1)
      .Values = Range("Y4").Cells(1, 2 + 2 * i).Resize(32000, 1)
    End With
  Next i
End Sub

This also ran fine, and the series references the expected ranges in the worksheet.

So I don't know what the problem was.

Upvotes: 0

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

Reputation: 38520

I've decided to add a sheet button that calls a sub that can add the series after the data has been entered. It also checks whether the various ranges are empty before actually adding each series. This feels like the cleanest solution.

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

You could try to copy the range (both columns), and use paste special to add it to the chart:

TwoColumnRange.Copy
chtMap.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
    CategoryLabels:=True, Replace:=False, NewSeries:=True

Upvotes: 1

Related Questions