vba 从数组数据而不是范围创建图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10570023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Chart from Array data and not range
提问by sifar
is it possible to create a Chart (e.g. Double Y-Axis Line Chart) not from Ranges, but from Array data? If so, how?
是否可以不从范围而是从数组数据创建图表（例如双 Y 轴折线图）？如果是这样，如何？
回答by Jean-Fran?ois Corbett
Yes. You can assign arrays to the XValuesand Valuesproperties of a Seriesobject on a chart. Example:
是的。您可以将数组分配给图表上对象的XValues和Values属性Series。例子：
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
回答by lori_m
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:
您可以将数组分配给 Excel 2007 以后的图表系列，但在以前的版本中，我相信每个系列的长度有 255 个字符的限制。以下随机游走示例显示了我用来解决此限制的方法：
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.
另一种方法是为数组分配名称（类似于上述解决方法），然后将系列设置为引用分配的名称。只要您以 xls 格式保存，这在所有版本中都可以正常工作，但是在保存为新的 xlsx/xlsm/xlsb 格式时，8192 个字符的命名数组似乎存在长度限制。
