vba Excel VBA更新图表系列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43814572/
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
Excel VBA Updating Chart Series
提问by Danny Yun
I have a code the copying the range to a certain cell and change the data source to the chart. the chart has combination chart type, which has stacked columns and xlLineMarkerwith last series(Grand Total). The number of the series collection varies based on user selections(2 or more).
我有一个将范围复制到某个单元格并将数据源更改为图表的代码。该图表具有组合图表类型，具有堆叠列和xlLineMarker最后一个系列（总计）。系列收藏的数量因用户选择（2 个或更多）而异。
It works, but sometimes the chart doesn't get fully updated like the image attached (displaying empty chart with series name as 'series1, series2, ...). When it happens, The right-click on the chart and click select data, and then simply selecting any series other than series1 update the chart. not refresh or re-assigning the data source. just selecting 'series2' or others in the data selection windows and click 'cancel', which I suspects that the macro doesn't fully executed...,
它可以工作，但有时图表不会像附加的图像那样完全更新（显示系列名称为“series1、series2……”的空图表）。发生这种情况时，右键单击图表并单击选择数据，然后只需选择 series1 以外的任何系列更新图表。不刷新或重新分配数据源。只需在数据选择窗口中选择“series2”或其他，然后单击“取消”，我怀疑宏没有完全执行...，
am I doing something wrong? or is there any way fully update the chart?
难道我做错了什么？或者有什么方法可以完全更新图表？
Sub chtupdate()
Dim rng As Range
Dim i As Integer
Dim n As Integer
Set rng = Range("G37").CurrentRegion
ActiveSheet.ChartObjects("Chart1").Activate
With ActiveChart
.ChartArea.ClearContents
.SetSourceData Source:=rng, PlotBy:=xlRows
n = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.Count
For i = 1 To n - 1
.SeriesCollection(i).ChartType = xlColumnStacked
Next i
.SeriesCollection(n).ChartType = xlLineMarkers
End With
End Sub
回答by Shai Rado
Try the code below, I've added ChartObjectand Seriesobjects variables for an easier and "cleaner" code. Also, to avoid the need of using ActiveSheetand ActiveChart- a method that be unreliable.
试试下面的代码，我添加了ChartObject和Series对象变量以获得更简单和“更干净”的代码。此外，为了避免使用ActiveSheet和ActiveChart- 一种不可靠的方法。
Code
代码
Option Explicit
Sub chtupdate()
Dim MyCht As ChartObject
Dim Ser As Series
Dim Rng As Range
Dim i As Long
Dim n As Long
' fully qualify all your Ranges and Cells
With Worksheets("Sheet3") ' <-- replace "Sheet1| with your sheet's name
Set Rng = .Range("G37").CurrentRegion
' set the chart object
Set MyCht = .ChartObjects("Chart1")
End With
With MyCht
.Chart.ChartArea.ClearContents
.Chart.SetSourceData Source:=Rng, PlotBy:=xlRows
For Each Ser In .Chart.SeriesCollection
Ser.ChartType = xlColumnStacked
Next Ser
' modify the last one
Set Ser = .Chart.SeriesCollection(.Chart.SeriesCollection.Count)
Ser.ChartType = xlLineMarkers
End With
End Sub
