嗨,我想将自定义数据标签添加到我的气泡图中。我的代码在下面。目前数据标签指的是XValues。我希望我的数据标签填充气泡大小。你介意帮我如何自定义下面的代码吗?
我尝试添加.DataLabel.Text="txt",但收到以下内容错误:运行时错误"438":对象不支持此属性或方法
Public Sub CreateMultiSeriesBubbleChart()
If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
MsgBox "Selection must have 4 columns and at least 2 rows"
Exit Sub
End If
Dim red, green, blue As Integer
Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=600, Top:=Selection.Top, Height:=400)
bubbleChart.Chart.ChartType = xlBubble
Dim r As Integer
For r = 2 To Selection.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
.Name = "=" & Selection.Cells(r, 1).Address(External:=True)
.XValues = Selection.Cells(r, 2).Address(External:=True)
.Values = Selection.Cells(r, 3).Address(External:=True)
.BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
.Format.Fill.Solid
.Format.Fill.ForeColor.RGB = RGB(61, 161, 161)
' .DataLabel.Text = "txt"
End With
Next
bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)
bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)
bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0
结束子
我的输入样本:
Label Hour Day count
01-SUNDAY 14 1 1
01-SUNDAY 19 1 1
02-MONDAY 12 2 1
02-MONDAY 13 2 1
02-MONDAY 14 2 2
02-MONDAY 16 2 2
在不使用VBA的情况下,右键单击气泡并选择"添加数据标签"。然后,在数据标签上单击鼠标右键,然后单击"设置数据标签格式"。在"标签选项"下,选择"来自单元格的值",然后指定包含要使用的标签的单元格。
DataLabel.Text
是Point
的方法,而不是NewSeries
此代码:
For r = 2 To Selection.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
[...]
.DataLabel.Text = "txt"
End With
Next
尝试标记该系列,但失败。
认识到这段代码来自另一个著名的"多系列气泡图"示例,我们合理地假设每个系列只需要处理1个数据点,这使得以下代码成为解决方案:
For r = 2 To Selection.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
[...]
.Points(1).HasDataLabel = True
.Points(1).DataLabel.Text = "txt"
End With
Next