Search code examples
excelvbaexcel-2010vba7

How to insert dynamically an horizontal line in a graph associated with the last value of a certain range of values

I am trying to automatically insert a horizontal line on a chart that receives 5 series of data from a table. With each new day, I add another row of values to that table. And I wanted to insert in the graph obtained from these 5 series, a horizontal line that was updated (its y coordinate) from the last value of one of these series which represents a consumption of flying water in the last 30 days. I wanted to use VBA within the Excel workbook. Can I ask for help? Thank you.

enter image description here

The graph would be like this: enter image description here

Solution

  • If adding an extra column is not viable, try this code:

    Sub SubExtraLine()
    
    'Declarations.
    Dim ObjChart As Object
    Dim RngCell As Range
    Dim DblCounter As Double
    Dim VarSeries As Variant
    Dim ObjSeries As Object
    Dim StrSeriesName As String
    
    'Settings.
    Set RngCell = Range("D1").End(xlDown) '‹ Specify here the cell with the value.
    Set ObjChart = Shapes("Chart 1") '‹ Specify here the chart.
    StrSeriesName = "Consumo em 30 dias" '‹ Specify here the name of the line.
    
    'Checking the maximum number of values for each series of ObjChart.
    For Each VarSeries In ObjChart.Chart.SeriesCollection
        DblCounter = Excel.WorksheetFunction.Max(DblCounter, UBound(VarSeries.Values))
        'If a series named as StrSeriesName already exists, it's deleted.
        If VarSeries.Name = StrSeriesName Then
            VarSeries.Delete
        End If
    Next
    
    'Setting VarSeries as the formula for the new series.
    Set VarSeries = Nothing
    VarSeries = "="
    For DblCounter = DblCounter To 1 Step -1
        VarSeries = VarSeries & RngCell.Parent.Name & "!" & RngCell.Address & ","
    Next
    VarSeries = Left(VarSeries, Len(VarSeries) - 1)
    
    'Creating the new series.
    Set ObjSeries = ObjChart.Chart.SeriesCollection.NewSeries
    ObjSeries.Name = "=""" & StrSeriesName & """"
    ObjSeries.Values = VarSeries
    
End Sub