Excel VBA 图表，仅在最后一点显示数据标签
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23632152/
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 chart, show data label on last point only
提问by user3632580
I want to add data labels to only the final point on my line graph, at the moment I am using the below, which works fine but only if I know what number the final point is. I've done a lot of searching and found the points(points.count) object in excel help but I can't seem to make it work for me. Please can you suggest a way of only showing the last point on my chart or (ideally) all charts on a worksheet.
我只想将数据标签添加到我的折线图的最后一点，目前我正在使用以下内容，这可以正常工作，但前提是我知道最后一点是什么数字。我已经做了很多搜索并在 excel 帮助中找到了 points(points.count) 对象，但我似乎无法让它对我有用。请您建议一种仅显示我的图表上的最后一点或（理想情况下）工作表上的所有图表的方法。
Sub Data_Labels()
'
' Data_Labels Macro
ActiveSheet.ChartObjects("Menck Chart").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Delete
ActiveSheet.ChartObjects("Menck Chart").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(59).Select
ActiveChart.SeriesCollection(1).Points(59).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 9
End Sub
回答by CodeJockey
Short Answer
简答
Dim NumPoints as Long
NumPoints = ActiveChart.SeriesCollection(1).Count
ActiveChart.SeriesCollection(1).Points(NumPoints).ApplyDataLabels
Long Answer
长答案
The use of ActiveChartis vague, and requires the additional step of selecting the chart of interest. If you specify the chart you are interested in explicitly, your macro will be much more robust and easier to read. I also recommend either using a Withblock, or creating intermediate variables, since reading ActiveChart.SeriesCollection(1).Pointsover and over is painful and clutters your code. Try the later method as follows:
的使用ActiveChart是模糊的，需要额外的步骤来选择感兴趣的图表。如果您明确指定您感兴趣的图表，您的宏将更加强大且易于阅读。我还建议使用With块，或创建中间变量，因为ActiveChart.SeriesCollection(1).Points一遍又一遍地阅读是痛苦的，并且会使您的代码变得混乱。试试后面的方法，如下：
Dim chartMenck As Chart, menckPoints as Points, menckDataLabel as DataLabel
Set chartMenck = Sheet1.ChartObjects("Menck Chart").Chart
Set menckPoints = chartMenck SeriesCollection(1).Points
menckPoints(menckPoints.Count).ApplyDataLabels
Set menckDataLabel = menckPoints(menckPoints.Count).DataLabel
menckDataLabel.Font.Size = 9
This is nearly half as long as the original and far easier to read, in my opinion.
在我看来，这几乎是原版的一半，而且更容易阅读。
回答by David Zemens
Try this. First it applies datalabels to ALL points, and then removes them from each point except the last one.
尝试这个。首先它将数据标签应用于所有点，然后从除最后一个点之外的每个点中删除它们。
I use the Points.Count - 1that way the For/Nextloop stops before the last point.
我使用Points.Count - 1这种方式For/Next循环在最后一点之前停止。
Sub Data_Labels()
'
Data_Labels Macro
Dim ws As Worksheet
Dim cht as Chart
Dim srs as Series
Dim pt as Point
Dim p as Integer
Set ws = ActiveSheet
Set cht = ws.ChartObjects("Menck Chart")
Set srs = cht.SeriesCollection(1)
'## Turn on the data labels
srs.ApplyDataLabels
'## Iterate the points in this series
For p = 1 to srs.Points.Count - 1
Set pt = srs.Points(p)
'## remove the datalabel for this point
p.Datalabel.Text = ""
Next
'## Format the last datalabel to font.size = 9
srs.Points(srs.Points.Count).DataLabel.Format.TextFrame2.TextRange.Font.Size = 9
End Sub
