Search code examples
excelvbacharts

Excel VBA Chart Not Plotting Second Series

I'm trying to plot two series on one chart, the first showing actuals over time and the second showing the maximum they are approaching. The first line shows up fine and the second series is created and shows up in the legend, but no data appears on the plot. Here's my simplified example:

Sub SimpleDebug()
    Dim test_sheet As Worksheet
    Set test_sheet = ThisWorkbook.Worksheets("TestData")
    Dim ch As Chart
    test_sheet.ChartObjects.Add Left:=750, Top:=50, Width:=400, Height:=300
    Set ch = test_sheet.ChartObjects(1).Chart
    Dim ser As Series
    Set ser = ch.SeriesCollection.NewSeries
    ser.ChartType = xlLine
    ser.XValues = test_sheet.Range("F2:F278")
    ser.Values = test_sheet.Range("K2:K278")
    ser.name = "Running Total"
    
    Dim max_val As Double
    max_val = 175000
    Set ser = ch.SeriesCollection.NewSeries
    ser.ChartType = xlLine
    Dim min_date As Date
    Dim max_date As Date
    min_date = test_sheet.Range("F2").Value
    Debug.Print "min_date: " & min_date
    max_date = test_sheet.Cells(278, 6).Value
    Debug.Print "max_date: " & max_date
    ser.XValues = Array(min_date, max_date)
    ser.Values = Array(max_val, max_val)
    ser.name = "Maximum"
End Sub

The debug print statements confirm that I'm getting the values that I expect from the sheet for min_date and max_date. Here are the results:

Chart generated by code

I'm at a loss for why the first series gets plotted just fine and the second simply doesn't appear. I have tried setting the second series as a totally independent variable rather than re-using 'ser' and it didn't make a difference.

Solution

  • Instead of using the min and max dates, just use the same XValues as the first Series, and create an array of the same size for the Values:

    Dim arr() As Double
ReDim arr(1 To test_sheet.Range("F2:F278").Count)
Dim i As Long
For i = LBound(arr) To UBound(arr)
    arr(i) = max_val
Next

ser.XValues = test_sheet.Range("F2:F278")
ser.Values = arr