Assign an array to .values and .Xvalues in an excel chart

Assign an array to .values and .Xvalues in an excel chart

• Amjath

  Not sure it's fancier but try this.
  
  Sub Jonas()
  
  Set MyDispl = Application.InputBox(Prompt:="Select the FIRST cell of the DISPLACEMENT data you want.", Type:=8)
  Set MyForce = Application.InputBox(Prompt:="Select the FIRST cell of the FORCE data you want.", Type:=8)
  
  i = 0
  Do
  If Len(MyDispl.Offset(i, 0).Value) = 0 Then Exit Do
  If Not IsNumeric(MyDispl.Offset(i, 0).Value) Then Exit Do
  i = i + 1
  Loop
  Set MyDispl = MyDispl.Resize(i)
  
  i = 0
  Do
  If Len(MyForce.Offset(i, 0).Value) = 0 Then Exit Do
  If Not IsNumeric(MyForce.Offset(i, 0).Value) Then Exit Do
  i = i + 1
  Loop
  Set MyForce = MyForce.Resize(i)
  
  With ActiveChart
  
  ''' make sure no series in chart from start
  Do While .SeriesCollection.Count > 0
  .SeriesCollection(.SeriesCollection.Count).Delete
  Loop
  
  .ChartType = xlXYScatterSmoothNoMarkers
  .SeriesCollection.NewSeries
  
  With .SeriesCollection(.SeriesCollection.Count)
  .Values = MyForce
  .XValues = MyDispl
  End With
  End With
  
  End Sub
  
  
  
  

• Christina Tabet

  Hi Andy,

  Yes I see the problem now. I have changed the code but somehow it hasn't been updated here I most done some bad. This is however how my code is now. It works fine but I don't like the part where I create a fake Range. If you know a fancier way pleace share.

  
  Set ws2 = ActiveSheet
  
  Set MyDispl = Application.InputBox(Prompt:="Select the FIRST cell of the DISPLACEMENT data you want.", Type:=8)
  
  
  Set MyForce = Application.InputBox(Prompt:="Select the FIRST cell of the FORCE data you want.", Type:=8)
  
  ReDim plot_disp(500)
  ReDim plot_force(500)
  i = 0
  ii = 0
  Do 'Collect displacement data
  temp_dir = MyDispl.Offset(i, 0)
  If Not IsNumeric(temp_dir) Then Exit Do
  If IsEmpty(temp_dir) Then Exit Do
  plot_disp(1, i) = MyDispl.Offset(i, 0)
  i = i + 1
  Loop

  Do 'Collect force data
  temp_for = MyForce.Offset(ii, 0)
  If Not IsNumeric(temp_for) Then Exit Do
  If IsEmpty(temp_for) Then Exit Do
  plot_force(1, ii) = MyForce.Offset(ii, 0)
  ii = ii + 1
  Loop
  
  ''' Create a range for the plot
  DisplFirst = MyDispl.Address
  DisplLast = MyDispl.Offset(r - 1, 0).Address
  ForceFirst = MyForce.Address
  ForceLast = MyForce.Offset(r - 1, 0).Address

  With ActiveChart
  
  ''' make sure no series in chart from start
  Do While .SeriesCollection.Count > 0
  .SeriesCollection(.SeriesCollection.Count).Delete
  Loop
  
  .ChartType = xlXYScatterSmoothNoMarkers
  .SeriesCollection.NewSeries

  With .SeriesCollection(.SeriesCollection.Count)
  .Values = ws2.Range(ForceFirst, ForceLast)
  .XValues = ws2.Range(DisplFirst, DisplLast)
  End With
  End with
  
  

  
  
  

• zot166249

  The problem is that the series formula in a chart has a length limit. And 2000 points is exceeding it, and then sum ;)
  
  Even though your data may not extend for 2000 points the arrays elements for all 2000 points is trying to be stored.
  
  Try this on some test data in range A1:B6. When the chart is created check the series formula and you should see why it will get very long very quickly. I think the limit is around 1024 characters.
  
  If possible you should use range references. If you need help adapting your loop post back.
  
  Not also I have changed the While condition test as it was only every picking up the first value.
  
  Sub Jonas()
  
  Dim myDispl
  Dim myForce
  Dim i, ii
  ReDim plot_disp(10)
  ReDim plot_force(10)
  
  Set myDispl = Application.InputBox(Prompt:="Select displ.", Type:=8)
  
  Set myForce = Application.InputBox(Prompt:="Select force.", Type:=8)
  
  Do '''Collects Displ data
  plot_disp(i) = myDispl.Offset(i, 0)
  i = i + 1
  Loop Until IsEmpty(myDispl.Offset(i, 0))
  
  Do '''Collects force data
  plot_force(ii) = myForce.Offset(ii, 0)
  ii = ii + 1
  Loop Until IsEmpty(myForce.Offset(ii, 0))
  
  Charts.Add
  With ActiveChart
  .ChartType = xlXYScatterSmoothNoMarkers
  .SeriesCollection.NewSeries
  With .SeriesCollection(.SeriesCollection.Count)
  .Values = plot_disp ''' This is the problem
  '''.Values = Array(plot_disp(1), plot_disp(2), ....) This works fine however
  '''.Values = Worksheets("Sheet1").Range("A27:A100") Also works
  .XValues = plot_force
  End With
  End With
  End Sub
  
  
  
  

• jaimlin

  You don't include details of what is selected using the Inputbox's
  or how you declare the variables plot_disp and plot_force.
  
  This worked for me when a range such as A1:A10 and B1:B10 where selected.
  
  Dim plot_disp
  Dim plot_force
  
  Set myDispl = Application.InputBox(Prompt:="Select displ.", Type:=8)
  
  Set myForce = Application.InputBox(Prompt:="Select force.", Type:=8)
  
  plot_disp = myDispl
  plot_force = myForce
  
  ' Do '''Collects Displ data
  ' plot_disp(i) = myDispl.Offset(i, 0)
  ' i = i + 1
  ' Loop Until IsEmpty(plot_disp(i))
  
  ' Do '''Collects force data
  ' plot_force(ii) = myForce.Offset(ii, 0)
  ' ii = ii + 1
  ' Loop Until IsEmpty(plot_force(ii))
  
  Charts.Add
  With ActiveChart
  .ChartType = xlXYScatterSmoothNoMarkers
  .SeriesCollection.NewSeries
  With .SeriesCollection(.SeriesCollection.Count)
  .Values = plot_disp ''' This is the problem
  '''.Values = Array(plot_disp(1), plot_disp(2), ....) This works fine however
  '''.Values = Worksheets("Sheet1").Range("A27:A100") Also works
  .XValues = plot_force
  End With
  End With
  
  
  
  
  
  

• Mario Cano

  I will have a look at this in 2007! Until then thanks to you Andy and the rest of you who helped me during this year.

  Merry Christmas and Happy new year

  
  
  

• tmiller3

  Hi Andy,

  Sorry for the lack of info. Yes it works as you says as long as one reads in a ranges. The problem however is that I'm letting the usr point to the first cell in each column that contains the numeric data. I therefore needs the Do loop which reads in data until it finds a non numeric cell or a blank cell, where it stops. It seems like after having collected the numeric data in the arrays plot_force and plot_disp the program doesn't recognize them as Ranges anymore. And then it doesn't work to assign them to the plot.

  The arrays plot_disp and plot_force I've just declared as arrays of a length I never will overshoot. Not that nice but easy to code. I however planing to use Redim Preserve to make it more stylish.

  ReDim plot_disp(2000)
  ReDim plot_force(2000)

  Best regards

  \Jonas

  
  
  
• Assign an array to .values and .Xvalues in an excel chart