excel - How to plot array in VBA?

Question

I want to create a function plot_array(arr As Variant) which will create plot based on element in array. On x axis I want to have numbers 1, 2,.., n which are indexes of array elements, and on y axis I want to have values stored in array. In other words

Example

Dim arr(9) As Variant
arr(0) = 0
arr(1) = 1
arr(2) = 5
arr(3) = 1
arr(4) = 5
arr(5) = 5
arr(6) = 1
arr(7) = 7
arr(8) = 6 

plot_array(arr) 

I tried to figure it about by running Macros and thinking how can I generalize this code to be working for any array, but I end up with nothing. Is there any possibility how it can be done ?

Answer 1

Try the next code, please. It will create a chart (xlLine type) and feed it with the array. You can change in the line .SeriesCollection.NewSeries.Values = arr1 arr1 with arr and obtain the same thing, if you put all the used number in the range "A1:A9":

Sub testPlotChartArray()
  Dim sh As Worksheet, cH As Chart, arr1, arr(1 To 9)
  
  Set sh = ActiveSheet
  arr1 = sh.Range("A1:A9").Value
  arr(1) = 0: arr(2) = 1: arr(3) = 5: arr(4) = 1: arr(5) = 5
  arr(6) = 5: arr(7) = 1: arr(8) = 7: arr(9) = 6
  On Error Resume Next
   Set cH = sh.ChartObjects("PlotChart").Chart
   If Err.Number = 0 Then
        Err.Clear
        cH.Parent.Delete
   End If
  On Error GoTo 0
  
  Set cH = sh.ChartObjects.Add(left:=60, top:=10, width:=300, height:=300).Chart
  With cH
    .Parent.Name = "PlotChart"
    .ChartType = xlLine
    .SeriesCollection.NewSeries.Values = arr1 'or arr
  End With
End Sub

The above code deletes the chart if it exists, but it can be configured to use the same existing chart and feed its .SeriesCollection(1).Values...