How to use an array variable to build a chart?

[Harry Covair]

Hi the forum,

I want to submit the forum the following problem:


In a VBA procedure, I feed an array variable (Dim Temp_Max () As Variant, ReDim Temp_Max(200)) with temperatures (numeric values):

the_temps_Max(i_temp) = Worksheets("DATA").Cells(x, 4).Value

Same reasoning with years

the_years(i_temp) = Year(Worksheets("DATA").Cells(x, 2))

In each case, debug.print shows the correct values.

As the SetSourceData method requires a range, and cannot use a Variant array, I have to use the individual components of the Series objects.

How to use these arrays in a chart?

I tried

Dim mychart As Object, Min_chart As Single, Max_chart As Single
Min_chart = WorksheetFunction.Min(the_temps_Max)
Max_chart = WorksheetFunction.Max(the_temps_Max)
Set mychart = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With mychart
  .Chart.Type = xlLine
  .Chart.SeriesCollection(1).xlValues = the_temps_Max' ERROR MESSAGE
  .Chart.SeriesCollection(1).XValues = the_years
  .Chart.HasLegend = False
  .Chart.Axes(xlValue, xlPrimary).MaximumScale = Max_chart
  .Chart.Axes(xlValue, xlPrimary).MinimumScale = Min_chart
  .Chart.SeriesCollection(1).Trendlines.Add
End With

But on “.Chart.SeriesCollection(1).XValues = Temps_Max”, it generates an error message “Application-defined or object-defined error”.


The final result should be something like:




I would really appreciate could some member of the forum help me.

Thank you in advance for your answer.

Harry

 

[Debaser]

You don't actually have any series on the chart at that point. Try:

With myChart.Chart
  .Type = xlLine
  .SeriesCollection.NewSeries
  .SeriesCollection(1).Values = the_temps_Max
  .SeriesCollection(1).XValues = the_years
  .HasLegend = False
  With .Axes(xlValue, xlPrimary)
  .MaximumScale = Max_chart
  .MinimumScale = Min_chart
  End With
  .SeriesCollection(1).Trendlines.Add
End With

 

[Harry Covair]

Hi Debaser,
Thanks for your answer.
Your solution is running fine.
Have a nice day

Harry