excel - 使用 VBA 向折线图添加标签

Question

现在我有以下代码来显示一条线曲线。输入的数量可能会有所不同，我希望图表在每次运行宏时清除并绘制一条新的线曲线。

Sub addchart()

    If ActiveSheet.ChartObjects.Count > 0 Then
        ActiveSheet.ChartObjects.Delete
    End If
    
    Dim ws As Worksheet
    Dim ch As chart
    Dim ch1 As chart
    Dim dt As Range
     
    Dim i As Integer
    
    i = Cells(Rows.Count, "I").End(xlUp).Row
    
    Set ws = ActiveSheet
    Set dt = Range(Cells(2, 10), Cells(i, 10))
    Set ch = ws.Shapes.AddChart2(Width:=1300, Height:=300, Left:=Range("a13").Left, Top:=Range("a13").Top).chart
    
    
    With ch
        .SetSourceData Source:=dt
        .ChartTitle.Text = "Deflection Curve"
        .ChartType = xlLine
        .SeriesCollection(1).Name = "Deflection"
    End With
    
    If Application.WorksheetFunction.Min(dt) > -50 Then
    With ch.Axes(xlValue)
        .MinimumScale = -50
        .MaximumScale = 0
    End With
    End If
    
End Sub

打印的图表看起来像这样

我试图弄清楚如何将标签添加到图表的任意点。两个标签要具体。一个是最小值。一个是 x 轴上任意点的值。两个 x 值都是已知的，并将作为工作表上两个单元格的输入。像这样的东西。

突出显示的风格并不重要。谢谢您的帮助!
附言- 我是 VBA 新手，我正在学习一切。我查找我需要做的事情，然后尝试模仿我在网上看到的任何示例。因此，我为图表编写的现有程序可能有不必要的步骤或在某些方面效率低下。如果有人能提供任何改进它的建议，我将不胜感激，即使它完成了工作。谢谢!

Answer 1

尝试制作图表标签的第一步:

Dim chartname as string

chartname = "enter_a_name"

ActiveSheet.Shapes.AddChart2(227, xlLine).Name = chartname
    With ActiveSheet.Shapes(chartname).Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Weight = 1.5
    End With
        
Set my_chart = ActiveSheet.ChartObjects(chartname).Chart
    
    'Delete all Autolabels
    my_chart.SetElement (msoElementDataLabelNone)
    
    'Enter format of axis (just if you want to)
    'With my_chart.Axes(xlCategory)      ' axis adjustment
        '.CategoryType = xlCategoryScale ' not XlCategoryType.xlAutomaticScale | XlCategoryType.xlTimeScale
        '.TickLabels.NumberFormat = "DD.MM.YYYY hh:mm"
        '.TickLabels.Orientation = xlUpward
    'End With
    
    cols = Array("F", "L")              ' columns containing labels
    For j = 1 To my_chart.SeriesCollection.Count
        Set sc = my_chart.SeriesCollection(j)

        For i = 2 To sc.Points.Count
            sc.Points(i).ApplyDataLabels
            sc.Points(i).DataLabel.Text = Range(cols(j - 1) & i + x).Value ' x= starting row containing values /labels
        Next i