我正在尝试使用VBA在其自己的工作表上创建xy散点图。我有两组数据要使用。当我第一次运行下面显示的代码时（即当不存在“功率图表”时），它将正确绘制数据。但是，当我重新运行带有“ Power Chart”的代码时，又出现了3个附加系列，其中两个包含空白数据，一个仅包含y值，对应于工作表中的最后一列。然后，当我第三次运行代码时，我再次获得正确的绘图。继续重新运行我的代码会重复执行好图->坏图->好图的循环。关于什么原因的任何想法？
Sub CreatingChartOnChartSheet()
Dim ch As Chart
Dim xrng As Range
Dim yrng1 As Range
Dim yrng2 As Range
Set ch = Charts.Add
Set xrng = Sheets("Power").Range("A2:A65536")
Set yrng1 = Sheets("Power").Range("D2:D65536")
Set yrng2 = Sheets("Power").Range("E2:E65536")
With ch
' If there is a previous chart, delete it
For Each Chart In ActiveWorkbook.Charts
If Chart.Name = "Power Chart" Then
Application.DisplayAlerts = False
Charts("Power Chart").Delete
Application.DisplayAlerts = True
End If
Next Chart
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""Series 1"""
.SeriesCollection(1).XValues = xrng
.SeriesCollection(1).Values = yrng1
.SeriesCollection.NewSeries
.SeriesCollection(2).Name = "=""Series 2"""
.SeriesCollection(2).XValues = xrng
.SeriesCollection(2).Values = yrng2
.SetElement (msoElementChartTitleAboveChart)
.Name = "Power Chart"
.ChartTitle.Text = "Power"
.SetElement (msoElementLegendRight)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
'.Axes(xlCategory).MajorUnit = 1
'.Axes(xlCategory).MinorUnit = 1
End With
End Sub
如果在工作表上选择了数据的情况下插入图表表或图表对象，则图表将自动绘制所选数据。每当通过VBA添加图表时，最好先删除任何已自动添加的系列。
Sub CreatingChartOnChartSheet()
Dim xrng As Range
Dim yrng1 As Range
Dim yrng2 As Range
With Sheets("Power")
Set xrng = .Range("A2:A65536")
Set yrng1 = .Range("D2:D65536")
Set yrng2 = .Range("E2:E65536")
End With
Application.DisplayAlerts = False
On Error Resume Next
Charts("Power Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With Charts.Add
'remove any auto-plotted data
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
With .SeriesCollection.NewSeries
.Name = "Series 1"
.XValues = xrng
.Values = yrng1
End With
With .SeriesCollection.NewSeries
.Name = "=""Series 2"""
.XValues = xrng
.Values = yrng2
End With
.SetElement msoElementChartTitleAboveChart
.Name = "Power Chart"
.ChartTitle.Text = "Power"
.SetElement (msoElementLegendRight)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
'.Axes(xlCategory).MajorUnit = 1
'.Axes(xlCategory).MinorUnit = 1
End With
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句