将数据标签添加到excel中序列中的最后一个点

I have around 50 charts in an excel worksheet which I should update every month. I need a VBA code which would add data labels to the last point in the data series, to all the charts in the worksheet. My each Chart has 3 series, but I need to add labels for that month's data(latest point) to ONLY ONE series which would be YTD amount. I have no knowledge in writing VBA code. I have seen couple codes and tried copying and making small changes, but no code suits my requirement. I don't want to make 50+ changes every month manually, so any thing that would address my issue would be very helpful. I greatly appreciate your help!

我在excel工作表中有大约50个图表,我应该每个月更新一次。我需要一个VBA代码,它将数据标签添加到数据系列的最后一个点,添加到工作表中的所有图表。我的每张图表都有3个系列,但我需要将该月份的数据(最新点)添加到只有一个系列,这将是年初至今的金额。我不懂编写VBA代码。我已经看过几个代码,并尝试复制和进行小的更改,但没有代码适合我的要求。我不想每个月手动进行50多次更改,因此解决我的问题的任何事情都会非常有用。非常感谢你的帮助!

1 个解决方案

#1

Something like this ought to get you started:

这样的事情应该让你开始:

Option Explicit

Private Sub AddLabelsLastPoint()

    Dim ch As ChartObject

    'Loop through each chart object
    For Each ch In Worksheets("Sheet3").ChartObjects

        'Grab the final series of the chart
        With ch.Chart.SeriesCollection(3)
            'Apply data label to final point in series
            .Points(.Points.Count).ApplyDataLabels
        End With

    Next ch


End Sub

Open the VB Editor with ALT+F11 (or go to Developer --> Visual Basic on the ribbon) and then right click the 'Microsoft Excel Objects' --> Insert --> Module

使用ALT + F11打开VB编辑器(或转到开发人员 - >功能区上的Visual Basic),然后右键单击“Microsoft Excel对象” - >“插入” - >“模块”

Be sure to change Worksheets("Sheet3") to whichever worksheet you are using. Additionally, this code assumes that your YTD amount will always be the last series, hence the SeriesCollection(3) of each chart. If this isn't the case, we will need to modify the code, but this is a starting point at least.

务必将Worksheets(“Sheet3”)更改为您使用的任何工作表。此外,此代码假定您的YTD金额将始终是最后一个系列,因此每个图表的SeriesCollection(3)。如果不是这种情况,我们将需要修改代码,但这至少是一个起点。

Since you say you have no knowledge of writing VBA, I will point out two other things (which are irrelevant to your problem, but good to know nonetheless).

既然你说你不知道写VBA,我会指出另外两件事(这与你的问题无关,但很高兴知道)。

First is the Option Explicit keyboard. This forces variables to be declared before they can be used. It is good practice to include this in your VBA code. If you don't include this, you may get unexpected results (if, for example, you misspell a variable name).

首先是Option Explicit键盘。这会强制变量在可以使用之前声明。最好将此包含在您的VBA代码中。如果不包括此项,则可能会出现意外结果(例如,如果拼错变量名称)。

Second is the Private keyword. This means that only code within the current module can access the subroutine.

第二个是Private关键字。这意味着只有当前模块中的代码才能访问子例程。

#1