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

Question

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.

The graph would be like this:

A workaround can be adding another column that has only 9.85 as value for every date and add a trend line for this columns values. Since all values are the same the trend line will be horizontal, and you can make the dots of these values invisible, so only the trend line remains. • How to do that with VBA? Try the macro recorder and checkout the code it produces to get a first idea. Try it yourself and if you got stuck or errors come back with your code (or a [mcve]) and ask a more precise question to it. — Pᴇʜ, Jan 20 '21 at 16:25
The last value in a column can easily be found with a formula like `=INDEX(B:B,SUMPRODUCT(MAX((B:B<>"")*ROW(B:B))))` which will result in the last value in column B. So there is not really VBA necessary. If you put this formula in the helper column for the trend line, it adjusts automatically whenever you enter new data. — Pᴇʜ, Jan 20 '21 at 16:29
It's also possible to add via VBA a series in the graph, if you don't want an extra column. Still the extra column is the easiest and quickest way. — Evil Blue Monkey, Jan 20 '21 at 16:32

score 0 · Accepted Answer · edited Jan 21 '21 at 07:30

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

I just had to make this change, and now all worked fine, thank you: Set ObjChart = ThisWorkbook.Worksheets("AguasCascais").ChartObjects("Chart 1") — ajsor, Jan 21 '21 at 09:44

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

1 Answers1