is it possible to create a Chart (e.g. Double Y-Axis Line Chart) not from Ranges, but from Array data? If so, how?
5
-
possible duplicate of To set dynamic data of excel chart at runtime and not Excel.Rangeassylias– assylias2012-05-13 07:57:36 +00:00Commented May 13, 2012 at 7:57
-
4@assylias: That is not an obvious duplicate... The language is C#, not VBA. The concepts are pretty much the same, but a worked out example won't look the same at all.Jean-François Corbett– Jean-François Corbett2012-05-13 14:43:30 +00:00Commented May 13, 2012 at 14:43
-
@assylias Plus i didn't view the other question as actually being answeredbrettdj– brettdj2012-05-14 00:13:18 +00:00Commented May 14, 2012 at 0:13
-
@Jean-FrançoisCorbett Agreed - I should have flagged as related, not duplicate.assylias– assylias2012-05-14 08:05:35 +00:00Commented May 14, 2012 at 8:05
-
@brettdj Agreed - I should have flagged as related, not duplicate.assylias– assylias2012-05-14 08:06:31 +00:00Commented May 14, 2012 at 8:06
Add a comment
|
2 Answers
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
answered May 13, 2012 at 14:40
Jean-François Corbett
38.7k30 gold badges145 silver badges192 bronze badges
4 Comments
John Alexiou
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
@ja72: Define "small"?... I just tested it with 16,000 points.
lori_m
@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)
Mihai Bratulescu
@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 arraysYou 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.
3 Comments
Jean-François Corbett
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
No the old Excel 4 commands don't seem to work on charts in later versions which is annoying.
lori_m
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.