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 ?

Are you willing to first store your values in worksheet cells ?? — Gary's Student
– Gary's Student, Commented Jan 6, 2021 at 11:37
Yes, I prefer solution to have them first in your worksheet cells ;)) — John
– John, Commented Jan 6, 2021 at 11:44
Refer this. An array of x-axis values is created on the x-axis, and a y-axis array is created and substituted on the y-axis. — Dy.Lee
– Dy.Lee, Commented Jan 6, 2021 at 14:57

FaneDuru · Accepted Answer · 2021-01-06 12:54:35Z

0

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...

answered Jan 6, 2021 at 12:54

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FaneDuru Over a year ago

@John: Didn't you find some time to check the above code?

Dy.Lee · Accepted Answer · 2021-01-07 08:39:00Z

If you declare an array as arr(9) in Excel, the index is declared as a total of 10 arrays from 0 to 9, so your array is not the correct number.

Sub test()
    Dim arr(8) 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)
    
End Sub
Sub plot_array(arr As Variant)
    Dim Srs As Series
    Dim Cht As Chart
    Dim xAxes As Axis, yAxes As Axis
    Dim i As Integer, vX() As Variant
    
    ReDim vX(LBound(arr) To UBound(arr))
    
    For i = LBound(arr) To UBound(arr)
        vX(i) = i + 1 '<~~ if index start from 0 then delete +1
    Next i
    
    Set Cht = ActiveSheet.Shapes.AddChart.Chart
    With Cht
        .HasTitle = True
        .ChartTitle.Text = "My Graph"
        .ChartType = xlXYScatterLinesNoMarkers
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        Set Srs = .SeriesCollection.NewSeries
        With Srs
            .Name = "item"
            .Values = arr
            .XValues = vX
            .MarkerStyle = xlCircle
        End With
        Set xAxes = .Axes(xlCategory, xlPrimary)
        With xAxes
            .MinimumScale = 0
            .MaximumScale = WorksheetFunction.Max(vX)
            .MajorUnit = 1
            .HasMajorGridlines = True
        End With
        Set yAxes = .Axes(xlValue, xlPrimary)
        With yAxes
            .MinimumScale = 0
            .MaximumScale = WorksheetFunction.Max(arr) + 1
            .MajorUnit = 1
            .HasMajorGridlines = True
        End With
            
        
    End With
End Sub

Collectives™ on Stack Overflow

How to plot array in VBA?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related