CODEWITHSUNDEEP
vbaexcelcharts
Janthelme
Janthelme

Reputation: 999

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

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) :

enter image description here

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).

enter image description here

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)?

Upvotes: 0

Views: 1110

Answers (1)

John Ink
John Ink

Reputation: 526

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

Upvotes: 1

Related Questions