vba 如何从数组制作图形？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25811947/
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
How to make graph from array?
提问by Amir
I would like to make a graph using a known array. The following code is working:
我想使用已知数组制作图表。以下代码正在运行：
Sub arraychart()
Dim cht As Object
Dim hhh As Variant
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Data").Range("a1:d29")
.Chart.Type = xlLine
.Left = 350
.Width = 400
.Top = 30
.Height = 200
End With
End Sub
But I want to use a array array1= Sheets("Data").Range("a1:d29") instead of
但我想使用数组 array1= Sheets("Data").Range("a1:d29") 而不是
.Chart.SetSourceData Source:=Sheets("Data").Range("a1:d29")
How could i code this? Thanks!
我怎么能编码这个？谢谢！
回答by David Zemens
You will have to use the individual components of the Seriesobject. The SetSourceDatamethod requires a range, and cannot use a Variant array.
您将不得不使用Series对象的各个组件。该SetSourceData方法需要一个范围，并且不能使用 Variant 数组。
Below example assumes X-values (category labels) in column A, and three series of data in columns B, C and D. Modify as needed.
下面的示例假设 A 列中有 X 值（类别标签），B、C 和 D 列中有三个系列的数据。根据需要进行修改。
Sub arraychart()
Dim cht As Object
Dim hhh As Variant
Dim srs as Series
Dim arrayValues as Variant
Dim arrayXValues as Variant
Dim rng as Range
Dim c as Integer
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
'Define the range you'll use:
Set rng = Range("A1:D29") 'modify as needed
'Define the array of X-Values to use in the chart
arrayXValues = rng.Columns(1).Value
With cht
.Chart.Type = xlLine
.Left = 350
.Width = 400
.Top = 30
.Height = 200
'Create each series in the chart
For c = 2 to rng.Columns.Count
'Define the array of values for each series
arrayValues = rng.Columns(c).Value
Set srs = .Chart.SeriesCollection.NewSeries
With srs
.XValues = arrayXValues
.Values = arrayValues
.Name = "whatever you want to name it, etc." 'Modify as needed
End With
Next
End With
End Sub
OUTPUT
输出
Here's an example chart from example data:
这是来自示例数据的示例图表：
NOTEThe only time I've ever done this was due to some corporate directive that our graphics application was supposed to produce charts which cannot easily (or mistakenly) be modified by users or consumers. So we spent a long time building, developing, and supporting this approach.
注意我唯一一次这样做是由于一些公司指令，我们的图形应用程序应该生成不能被用户或消费者轻易（或错误地）修改的图表。所以我们花了很长时间来构建、开发和支持这种方法。
It makes the "data" independent of the sheet/calculation/etc. While this has some uses, like it prevents users from inadvertently "breaking" the chart, it also makes it hard or impossible for the average user to update or modify the chart intentionally.
它使“数据”独立于工作表/计算/等。虽然这有一些用途，比如它可以防止用户无意中“破坏”图表，但它也使普通用户很难或不可能有意更新或修改图表。
And, it goes against everything users expect about charts, you can't use them to easily visualize changes in the data, you can't easily modify them by adding new series, or removing existing series on the Worksheet, etc. There are probably other reasons to hate this approach...
而且，它与用户对图表的期望背道而驰，您无法使用它们轻松地将数据中的变化可视化，您无法通过添加新系列或删除工作表上的现有系列等方式轻松修改它们。可能有讨厌这种方法的其他原因......
In short, I would not recommend doing it this way unless your paycheck depends on it. Ultimately, they asked us to go back to "normal" charts using the SetSourceDataor Series.Formulamethods.
简而言之，除非你的薪水取决于它，否则我不建议这样做。最终，他们要求我们使用SetSourceData或Series.Formula方法返回“正常”图表。
