VBA使用多个数据序列创建xy散点图
VBA使用多个数据序列创建xy散点图
提问于 2020-08-20 03:45:35
我正在尝试使用VBA在自己的工作表上创建xy散点图。我有两组数据要用。当我第一次运行下面显示的代码时(即，当没有"Power Chart“时)，它正确地绘制了数据。但是，当我重新运行带有"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回答 1
Stack Overflow用户
回答已采纳
发布于 2020-08-20 04:34:06
如果在工作表上有选定的数据时插入图表工作表或图表对象，图表将自动绘制选定的数据。每当通过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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63493953
复制相关文章
相似问题
腾讯云开发者
