excelvbacharts

Plot two series on one chart


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.
The second series is created and shows in the legend, but no data appears on the plot.

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 get the values that I expect from the sheet for min_date and max_date.

Here are the results:
Chart generated by code

The second series doesn't appear.

I tried setting the second series as an 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