[VBA] adding a band to a line-chart programmatically

[smallxyz]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Using 2 additional helper rows to set band
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Inventory	71	53	58	29	30	78	1	76	82	71	4	41
4	Lower Band Value	60	60	60	60	60	60	60	60	60	60	60	60
5	Upper Band Value	70	70	70	70	70	70	70	70	70	70	70	70
6
7
8
9	Without using 2 additional helper rows
10		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
11	Inventory	71	53	58	29	30	78	1	76	82	71	4	41
工作表2

Is it possible to add colored band ( on value axis )to a line chart via VBA, such that no additional helper rows are needed for adjustment ( the table below )?
i.e. user simply runs the code and edit the band width within the VBE.


Thanks a lot!

 

[pgc01]

Hi

See if this helps.

' add band to line chart
Sub AddBand()
Dim ws As Worksheet
Dim cht As Chart
Dim dArray() As Double
Dim lMin As Long, lMax As Long
Dim lPoints As Long, j As Long

lMin = 3 ' minimum band value
lMax = 5 ' maximum band value

Set ws = Worksheets("Sheet3") ' worksheet where the chart is located
Set cht = ws.ChartObjects(1).Chart ' first chart in worksheet

' adds the 2 series for the band
With cht

    ReDim dArray(1 To .SeriesCollection(1).Points.Count)
    
    ' add the second series (below band)
    
    With .SeriesCollection.NewSeries
        .ChartType = xlColumnStacked
        .Name = "Series 2"
        For j = 1 To UBound(dArray): dArray(j) = lMin: Next j
        .Values = dArray
        
        ' make it invisible
        .Format.Fill.Visible = msoFalse
        .Format.Line.Visible = msoFalse
    End With

    ' add the third series (between Min and Max of the band)
    
    With .SeriesCollection.NewSeries
        .ChartType = xlColumnStacked
        .Name = "Series 3"
        For j = 1 To UBound(dArray): dArray(j) = lMax - lMin: Next j
        .Values = dArray
    End With

    ' no gap so that is a band
    .ChartGroups(1).GapWidth = 0


End With
End Sub

 

[smallxyz]

Thanks PGC!!

 

[pgc01]

You're welcome.