【VBA】制作散点图及打标签VBA

colipso 2021-12-09 原文

【VBA】制作散点图及打标签VBA

1、散点图的制作必须只选择xy轴对应的数据，不能选中标签。

2、调整xy轴交叉点，改为四个象限。

3、通过菜单移动散点图到新表。

4、运行宏，打上文本标签。

VBA如下

 1 Sub AttachLabelsToPoints()
 2 
 3    \'Dimension variables.
 4    Dim Counter As Integer, ChartName As String, xVals As String
 5 
 6    \' Disable screen updating while the subroutine is run.
 7    Application.ScreenUpdating = False
 8 
 9    \'Store the formula for the first series in "xVals".
10    xVals = ActiveChart.SeriesCollection(1).Formula
11 
12    \'Extract the range for the data from xVals.
13    xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
14       Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
15    xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
16    Do While Left(xVals, 1) = ","
17       xVals = Mid(xVals, 2)
18    Loop
19 
20    \'Attach a label to each data point in the chart.
21    For Counter = 1 To Range(xVals).Cells.Count
22      ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
23          True
24       ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
25          Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
26    Next Counter
27 
28 End Sub