Create Chart from Array data and not range

arrays excel vba charts range

62,563

Solution 1

Yes. You can assign arrays to the XValues and Values properties of a Series object on a chart. Example:

Dim c As Chart
Dim s As Series
Dim myData As Variant

Set c = ActiveChart ' Assumes a chart is currently active in Excel...
Set s = c.SeriesCollection(1)

myData = Array(9, 6, 7, 1) ' or whatever
s.Values = myData

Solution 2

You can assign arrays to chart series in Excel 2007 onwards but in previous versions I believe there is a 255 character limit for the length of each series. A method I have used to work around this restriction is shown in the following random walk example:

Sub ChartArray()

Dim x(0 To 1000, 0 To 0) As Double
Dim y(0 To 1000, 0 To 0) As Double
x(0, 0) = 0
y(0, 0) = 0
For i = 1 To 1000
    x(i, 0) = i
    y(i, 0) = y(i - 1, 0) + WorksheetFunction.NormSInv(Rnd())
Next i

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
With ActiveChart.SeriesCollection
    If .Count = 0 Then .NewSeries
    If Val(Application.Version) >= 12 Then
        .Item(1).Values = y
        .Item(1).XValues = x
    Else
        .Item(1).Select
        Names.Add "_", x
        ExecuteExcel4Macro "series.x(!_)"
        Names.Add "_", y
        ExecuteExcel4Macro "series.y(,!_)"
        Names("_").Delete
    End If
End With
ActiveChart.ChartArea.Select

End Sub

An alternative method is to assign names to the arrays (similar to above workaround) and then set the series to refer to the assigned names. This works ok in all versions as long as you save in xls format, but there appears to be a length limitation for named arrays of 8192 characters when saving to the new xlsx/xlsm/xlsb formats.

62,563

Author by

sifar

Updated on November 20, 2020

Comments

sifar over 3 years

is it possible to create a Chart (e.g. Double Y-Axis Line Chart) not from Ranges, but from Array data? If so, how?
John Alexiou almost 12 years

This is a limit on the size of the data you can do like this. I cannot remember what it is, but it is small.
Jean-François Corbett almost 12 years

@ja72: Define "small"?... I just tested it with 16,000 points.
Jean-François Corbett almost 12 years

Out of curiosity, does the ExecuteExcel4Macro trick not work in 2007 and later? It tried it out in Excel 2007 and the chart contains no data.
lori_m almost 12 years

No the old Excel 4 commands don't seem to work on charts in later versions which is annoying.
lori_m almost 12 years

@ja72 is probably referring to Excel 2003 and prior where there was a length restriction. The grid size also limits the size of 1d-arrays to 256 or 16384 points in Excel 2003/2007 respectively. To overcome this you can define data as a vertical array by using application.transpose(mydata) (or declaring a nx1 array as in other response)
lori_m almost 12 years

I wouldn't say awful - convoluted maybe :) But I'm not sure there's another way, all web resources I could find said this was not possible in older versions.
Mihai Bratulescu almost 10 years

@lori_m I'm setting a List<double> whatever.toArray() for series.Values and running Excel 2007 but it only displays 256 points. Why? 16k would be enough (practical reasons) so I don't need to hack an unlimited number using nx1 arrays