Create series/line for chart from array

[mburrows]

Hi all,

I would like to add a horizontal line at a 'dynamic' (value taken at time of chart creation and then fixed) point. I would ideally prefer to not use a range, as this will mean i have a column with the same number repeated. I think a way around this is using arrays, but I have never used an array in vba before.

I create the chart from data in a range, this data is pulled from another sheet using a macro, and varys in size. I assume the size of the array would have to change to fit the first set of data.

Below is the code i use to create the chart. Test, Units and Spec are set earlier in the code, and I would like the horiztonal line to be at the value of Spec.

Dim Test As String
Dim Units As String
Dim cht As ChartObject
Dim rng As Range
Dim Spec As Double

'......other code not related to the graph creation......

Set rng = Range(Range("E4:F4"), Range("E4:F4").End(xlDown))

Set cht = ActiveSheet.ChartObjects.Add(Left := Range("H7").Left, Width := 450, Top := Range("H7").Top, Height := 250) 
    
cht.Chart.SetSourceData Source:=rng
cht.Chart.ChartType = xlLine
cht.Chart.HasTitle = True
cht.Chart.ChartTitle.Text = Test
cht.Chart.HasLegend = False
cht.Chart.SeriesCollection(1).MarkerStyle = xlMarkerStyleDiamond
cht.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
cht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = Units

Thanks!

 

[pgc01]

Hi

Try adding at the end of your code:

' add a new Scatter series
Dim ser As Series
Set ser = cht.Chart.SeriesCollection.NewSeries

' add the 2 points for the horizontal line
Spec = 2.5
With ser
    .ChartType = xlXYScatterLinesNoMarkers
    .XValues = Array(1, cht.Chart.SeriesCollection(1).Points.Count)
    .Values = Array(Spec, Spec)
End With

 

[mburrows]

That part worked perfectly! Thanks!

Unfortunately it has messed with some earlier code which was supposed to test if there was a chart relating to a specific range and if so delete it, but no other charts.
(This was very gratefully written by John_w)
The line in red gives an Object required error.

  Dim chartContainsDataRange As Range
    Dim chartObj As ChartObject
    Dim chartSeries As Series
    Dim seriesRange As Range
    Dim seriesParts As Variant
    Dim n As Long
    Dim deleteChart As Boolean
    
    'A chart will be deleted if its source data contains this specific data range, including the sheet reference
    
    Set chartContainsDataRange = ActiveSheet.Range("E4:F4")
    
    'Look at charts on the active sheet
    
    For Each chartObj In ActiveSheet.ChartObjects
        deleteChart = False
        
        For Each chartSeries In chartObj.Chart.SeriesCollection
            
            'Parse this =SERIES formula and look at X values range and Y values range arguments.
            '=SERIES(Series name, X values range, Y values range, Plot order)
            'The Split assumes that a comma does not occur in source data sheet name, otherwise the code following won't work

            seriesParts = Split(Mid(chartSeries.Formula, Len("=SERIES(") + 1), ",")
            For n = 1 To UBound(seriesParts) - 1
                If seriesParts(n) <> "" Then
                    [B][COLOR=#ff0000]Set seriesRange = Evaluate(seriesParts(n))[/COLOR][/B]
                    If Not Intersect(seriesRange, chartContainsDataRange) Is Nothing Then                                'looks at sheet and range references
                    'If Not Intersect(Range(seriesRange.Address), Range(chartContainsDataRange.Address)) Is Nothing Then  'looks at range reference only
                        'Chart data is within specified data range so can be deleted
                        deleteChart = True
                    End If
                End If
            Next
            
        Next
        
        If deleteChart Then
        
           chartObj.Delete
            
        End If
        
    Next

Any ideas how to edit either piece of code so they both work?

Thanks!

 

[pgc01]

Hi

(not tested)

Your

    Set seriesRange = Evaluate(seriesParts(n))

only works if the Evaluate() returns a range object, which in this case will not happen.

What I'd do is to first check if the Evaluate() returns a range object, and only in that case do the assignment, like:

[COLOR=#800000]Dim sSeriesPartType As String[/COLOR]

...

            seriesParts = Split(Mid(chartSeries.Formula, Len("=SERIES(") + 1), ",")
            For n = 1 To UBound(seriesParts) - 1
[COLOR=#800000]                sSeriesPartType = VarType(Evaluate(seriesParts(n)))
                If sSeriesPartType = "Range" Then
[/COLOR]                    Set seriesRange = Evaluate(seriesParts(n))
                 ...

Remark: BTW, the way you are spiting the series using Split() only works in some simple formulas, but I guess is enough in this case.