Put multiple data series on same graph with vba

[olorin_117]

Hello. I have the code below that when the user inputs a value on a certain cell and logs it via a button, this is called to populate a simple line graph. At the beginning of each day the graph area is empty. I have tried to add a second data series but nothinh seems to work and can't figure out why.Try to add a seccon series collection but i get a bug every time. Any suggestions will be appreciated. I am working on excel 2016

Sub ChartLinks(seconds As Variant)

Dim NewValues() As Variant
Dim NewXAxis() As Variant
    
Worksheets(1).ChartObjects(1).Activate
    
If ActiveChart.SeriesCollection.Count = 0 Then

    ActiveChart.SeriesCollection.NewSeries
    
End If
      
For Each x In ActiveChart.SeriesCollection
        
    NewValues = x.Values
    ReDim Preserve NewValues(1 To UBound(NewValues) + 1) As Variant
    seconds = CDbl(seconds)
    NewValues(UBound(NewValues)) = seconds
    x.Values = NewValues
    ActiveSheet.Unprotect
    NewXAxis = x.XValues
    NewXAxis(UBound(NewXAxis)) = UBound(NewXAxis)
    x.XValues = NewXAxis
    ActiveSheet.Protect
    
Next x
              
End Sub

 

[Jon Peltier]

You have added a new series using ActiveChart.SeriesCollection.NewSeries, but you have not populated it with any data. Looks like you're trying to then see what is present in the chart, and I guess you're trying to put some information into the arrays of values, but I can't figure out what.

FWIW, while you can manipulate arrays and use these arrays as series data in a chart, it's always more reliable to put the data into the sheet, and use the ranges in the sheet as the chart data.

 

[olorin_117]

You have added a new series using ActiveChart.SeriesCollection.NewSeries, but you have not populated it with any data. Looks like you're trying to then see what is present in the chart, and I guess you're trying to put some information into the arrays of values, but I can't figure out what.

FWIW, while you can manipulate arrays and use these arrays as series data in a chart, it's always more reliable to put the data into the sheet, and use the ranges in the sheet as the chart data.

Unfortunately the code isn't mine so I cant even figure out how it is working.I am not extremely exprerienced in vba. I found it already working as it is but now I dont know how to change it. I am thinking of sending the user input at a column and then 'pick it up' by the chart..It is do-able, right? I mean to have the chart update withe the press of a button based on the new input

 

[Jon Peltier]

Okay, I looked over the code, and it sort of works. I went over it and reworked it per my own style, and I included code that is called by a button to add a value to the chart.

The first routine is called by the button. It checks whether the value in the cell is a number, and if so it passes the value to the next routine.

The next routine checks whether the chart has any series in it. If not it adds a new series with 1 as the X value and the new value as the Y value. If there is a series, it extracts the X and Y values into arrays, adds an element to the end of each array, puts the next counting number into the last X array element and the new value into the last Y array element, then puts these arrays into the chart series.

When the second routine finishes, it returns to the first, and the new value cell is cleared.

One important question regards your note that "At the beginning of each day the graph area is empty." How is the chart cleared?

Sub AddValueButtonClick()
  Dim NewValueCell As Range
  Set NewValueCell = ActiveSheet.Range("B2")
  
  Dim NewValue As Variant
  NewValue = NewValueCell.Value
  
  If IsNumeric(NewValue) Then
    ChartLinks NewValue
  End If
  
  NewValueCell.ClearContents
End Sub

Sub UpdateChart(NewValue As Variant)
  Dim TheChart As Chart
  Set TheChart = ActiveSheet.ChartObjects(1).Chart
  
  If TheChart.SeriesCollection.Count = 0 Then
    ActiveSheet.Unprotect
    With TheChart.SeriesCollection.NewSeries
      .XValues = Array(1)
      .Values = Array(seconds)
    End With
    ActiveSheet.Protect
  Else
    With TheChart.SeriesCollection(1)
      Dim YValues As Variant, XValues As Variant
      YValues = .Values
      XValues = .XValues
      
      ReDim Preserve YValues(1 To UBound(YValues) + 1)
      YValues(UBound(YValues)) = CDbl(NewValue)
      ReDim Preserve XValues(1 To UBound(XValues) + 1)
      XValues(UBound(XValues)) = UBound(XValues)
      
      ActiveSheet.Unprotect
      .Values = YValues
      .XValues = XValues
      ActiveSheet.Protect
    End With
  End If
End Sub

 

[olorin_117]

Okay, I looked over the code, and it sort of works. I went over it and reworked it per my own style, and I included code that is called by a button to add a value to the chart.

The first routine is called by the button. It checks whether the value in the cell is a number, and if so it passes the value to the next routine.

The next routine checks whether the chart has any series in it. If not it adds a new series with 1 as the X value and the new value as the Y value. If there is a series, it extracts the X and Y values into arrays, adds an element to the end of each array, puts the next counting number into the last X array element and the new value into the last Y array element, then puts these arrays into the chart series.

When the second routine finishes, it returns to the first, and the new value cell is cleared.

One important question regards your note that "At the beginning of each day the graph area is empty." How is the chart cleared?

Sub AddValueButtonClick()
  Dim NewValueCell As Range
  Set NewValueCell = ActiveSheet.Range("B2")
 
  Dim NewValue As Variant
  NewValue = NewValueCell.Value
 
  If IsNumeric(NewValue) Then
    ChartLinks NewValue
  End If
 
  NewValueCell.ClearContents
End Sub

Sub UpdateChart(NewValue As Variant)
  Dim TheChart As Chart
  Set TheChart = ActiveSheet.ChartObjects(1).Chart
 
  If TheChart.SeriesCollection.Count = 0 Then
    ActiveSheet.Unprotect
    With TheChart.SeriesCollection.NewSeries
      .XValues = Array(1)
      .Values = Array(seconds)
    End With
    ActiveSheet.Protect
  Else
    With TheChart.SeriesCollection(1)
      Dim YValues As Variant, XValues As Variant
      YValues = .Values
      XValues = .XValues
     
      ReDim Preserve YValues(1 To UBound(YValues) + 1)
      YValues(UBound(YValues)) = CDbl(NewValue)
      ReDim Preserve XValues(1 To UBound(XValues) + 1)
      XValues(UBound(XValues)) = UBound(XValues)
     
      ActiveSheet.Unprotect
      .Values = YValues
      .XValues = XValues
      ActiveSheet.Protect
    End With
  End If
End Sub

Hello and thank you very much for your answer..I m gonna test it on my code now.The chart is emptied via the

.Chartobjects(1).Chart.ChartArea.ClearContents