Excel Chart - How to draw discontinuous series in VBA without range reference

Question

Excel 2010.

Issue : I need to plot a *single* *discontinuous* series in a XY-scatter chart *via VBA* without referencing a range in the sheet.

It is easy to achieve that when the Yvalues are laid-out in a sheet range, by inserting blank values at the discontinuities ; as long as one selects 'Show empty cells as: Gaps' in Select Data > Hidden and Empty Cells. Here is an example (the Series2 in red is the one that matters) :

So I was trying to reproduce the same via VBA :

Sub addDiscountinuousSingleSeries()
    Dim vx As Variant, vy As Variant
    Dim chrtObj As ChartObject, chrt As Chart, ser As Series

    Set chrtObj = ActiveSheet.ChartObjects("MyChart"): Set chrt = chrtObj.Chart

    Set ser = chrt.SeriesCollection.NewSeries

    vx = Array(0.3, 0.3, 0.3, 0.7, 0.7, 0.7)
    vy = Array(-1, 1, vbNullString, -1, 1, vbNullString)
    'vy = Array(-1, 1, CVErr(xlErrNA), -1, 1, CVErr(xlErrNA)) 'doesn't work either
    'vy = Range(RANGE_YVALUES_WITH_BLANK) 'this would work, but I do not want to reference a range

    chrt.DisplayBlanksAs = xlNotPlotted 'VBA equivalent to 'Show empty cells as: Gaps'

    With ser
        ser.Name = "VBA Series"
        .XValues = vx
        .Values = vy
    End With

End Sub

But the blank values in the vy array seems to be ignored and the two vertical bars are now connected, which I am trying to avoid (green series).

I know that I could delete the middle line programmatically, but in the real-life problem I am trying to solve it would not be the right solution (too complex, too slow).

My question : is there a way to specify the series' .Values array to get the expected behavior and get a gap between the two vertical green segments in vba (with only one series and no reference to a sheet range)?

I realized that this OP had a similar issue, but did not get any answer. — Janthelme
Not sure wheter available in Excel 2010 but for 2016 vy = Array(-1, 1, CVErr(xlErrNA), -1, 1, CVErr(xlErrNA)) works in combination with chrt.DisplayValueNotAvailableAsBlank = True. — Axel Richter
Thank you! The DisplayValueNotAvailableAsBlank property does not seem to exist in Excel 2010, so that might have been an upgrade after 2010, unfortunately. — Janthelme
Yes, found Display empty cells, null (#N/A) values, and hidden worksheet data in a chart: "Show #N/A as an empty cell option: This feature is only available if you have an Office 365 subscription and is currently only available to Insiders. If you are an Office 365 subscriber, make sure you have the latest version of Office." So not in Excel 2010. — Axel Richter
Can you not just split the array into two separate series when there is a null Value? I know this isn’t what you asked, but it would achieve the same visual effect. — Sercho

John Ink John Ink · Accepted Answer · 2017-12-31T12:39:34

You could just format the lines you don't want. Maybe not the prettiest way, but it'd achieve what your after.

ser.Points(3).Format.Line.Visible = msoFalse
ser.Points(4).Format.Line.Visible = msoFalse
ser.Points(6).Format.Line.Visible = msoFalse

Or:

For i = 1 To ser.Points.Count
    If i <> 1 Then k = i - 1 Else k = i
    If ser.Values(i) = 0 Or ser.Values(k) = 0 Then
        ser.Points(i).Format.Line.Visible = msoFalse
    End If
Next

Excel Chart - How to draw discontinuous series in VBA without range reference

1 Answers