Using difference of two ranges as a Series range

[theguyinthehat]

I'm trying to use the difference in values between two ranges as a range--the following code should convey what I'm trying to do, but VBA doesn't accept it. Is there a way to make this work without hosting the difference between the two columns in a third range, then using that for the series range? I'd rather not have it show on the worksheet.

ActiveChart.SeriesCollection(i + 2).Values = WS.Range(WS.Cells(26, 14), WS.Cells(wsrow, 14)) - WS.Range(WS.Cells(26, averagecolumn), WS.Cells(wsrow, averagecolumn))

[dmaruca]

The Series.Values property only takes ranges and arrays. You will have to calculate your values and feed the array to the series. Here is an example. The XValues are in the A column, and we are adding the B and C columns.

Sub SeriesExample()
    Dim data As Variant
    Dim out As Variant
    Dim sht As Worksheet
    Dim cht As ChartObject
    Dim i As Long
    
    Set sht = ActiveSheet
    Set cht = sht.ChartObjects(1)
    
    data = sht.Range("B1:C10").Value
    
    ReDim out(LBound(data, 1) To UBound(data, 1))
    
    For i = LBound(data, 1) To UBound(data, 1)
        out(i) = data(i, 1) + data(i, 2)
    Next
    
    'Add a new calculated series
    cht.Chart.SeriesCollection.NewSeries
    i = cht.Chart.SeriesCollection.Count
    With cht.Chart.SeriesCollection(i)
        .XValues = sht.Range("A1:A10")
        .Values = out
        .Name = "Calculated"
    End With
End Sub

[theguyinthehat]

Thanks--but I'm dealing with thousands of datapoints, and I know that series notation is capped at 275 characters or close to that.

[dmaruca]

It doesn't matter how many data points you are using. Values only takes a range or an array. You have to work with it.

[theguyinthehat]

Thank you that worked very well.

[theguyinthehat]

Nevermind, I tried it with an array of 200 rows and the program bugged out. I'd read there were problems like this with arrays--the array can only get so big before it doesn't work anymore. I guess this means I'm going to have to populate a range on the spreadsheet?

[dmaruca]

The method I gave for populating arrays from a range is the fastest you will find. I've personally used it for years with 10k+ rows. How did it bug out? It's probably a limitation of the chart. The human eye can't see 100,000 points on a small graph anyways. Why not only use every 1000 or so?

[theguyinthehat]

I'll let you know the precise error and upload the relevant code tomorrow. My spreadsheet is populated by sql queries so I can't access it from home