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轴线图)?如果是这样,如何?
2 个解决方案
#1
13
Yes. You can assign arrays to the XValues and Values properties of a Series object on a chart. Example:
是的。您可以将数组分配给图表上一个系列对象的XValues和Values属性。例子:
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
#2
8
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:
您可以在excel2007中为图表系列分配数组，但是在以前的版本中，我认为每个系列的长度都有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个字符的命名数组的长度似乎有限制。
#1
13
Yes. You can assign arrays to the XValues and Values properties of a Series object on a chart. Example:
是的。您可以将数组分配给图表上一个系列对象的XValues和Values属性。例子:
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
#2
8
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:
您可以在excel2007中为图表系列分配数组，但是在以前的版本中，我认为每个系列的长度都有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个字符的命名数组的长度似乎有限制。