Plot XY-Scatter diagram with Excel-VBA using arrays and change horizontal (category) Axis Label

Question

I am relatively new to Excel VBA and I am trying to learn how to plot charts with VBA script. I have the following data table (Figure1):

From this data table I would like to plot a graph (Figure2) as follows. I have plotted the chart with Excel's inbuilt function using line with markers chart type:

I have written a simple code in VBA to plot a similar type of chart, but with arrays instead of directly selecting the range from the sheet. My VBA code is as follows:

Sub plot_test2()

Dim ws2 As Worksheet
Dim i, j, c, m, n, a, lrow As Long
Dim frist_code, frist_value, frist_name As Variant
Dim xychart As Chart
Set ws2 = Worksheets("Sheet2")
i = 1: j = 1: a = 1
c = 4: lrow = 6: m = 2: n = 1

ReDim frist_code(i To lrow - 1, j To c)
ReDim frist_value(i To lrow - 1, j To c)
ReDim frist_name(i To lrow - 1, j To c)

For i = 1 To lrow - 1
    For j = 1 To c
        frist_value(i, j) = ws2.Cells(m, n)
        frist_code(i, j) = j
        frist_name(i, j) = ws2.Cells(1, n)
        n = n + 1
    Next j
    n = 1
    m = m + 1
Next i

Set xychart = ws2.Shapes.AddChart2(332, xlXYScatter, Left:=0, Top:=0, Width:=400, Height:=300).Chart

For i = 1 To lrow - 1
    For j = 1 To c
        xychart.SeriesCollection.NewSeries
        With xychart.SeriesCollection(a)
            .name = frist_name(i, j)         'series names are assigned by frist_name array 
            .Values = frist_value(i, j)      'series values are assigned by frist_value array
            .XValues = frist_code(i, j)      'series XValues are assigned by frist_code array
            .MarkerSize = 15
        End With
        a = a + 1
    Next j
    j = 1
Next i
xychart.Axes(xlCategory).TickLabelPosition = xlLow
End Sub

When I run my macro, I get the following plot (Figure3):

I wish to rename the horizontal axis labels to A, B, C, D (just like figure 2) instead of 1,2,3,4 using in the VBA macro using arrays. I have been trying to solve this problem, but I could not find the right solutions elsewhere.

I have also tried to plot the chart using xlLineMarkers instead of xlXYScatter Chart type. But the points were plotted on the same line.

I have also tried to use xychart.Axes(xlCategory).CategoryNames = Array ("A", "B", "C", "D"). But there is a compilation error.

Please let me know if anyone is able to figure out the solution or mistake is my VBA code. Thanks a ton!

Jon Peltier Jon Peltier · Accepted Answer · 2020-09-22T00:29:44

To use text labels, it can't be an XY scatter chart.

What you have is line chart type data. You need to plot as a line chart, plotted by row, lines with markers, then hide the lines. Also I made the markers larger since that's how it looked in your chart.

This is a quickie I recorded and modified:

Sub Macro1()
  ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
  ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$6"), PlotBy:=xlRows
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    srs.Format.Line.Visible = msoFalse
    srs.MarkerSize = 8
  Next
End Sub

Plot XY-Scatter diagram with Excel-VBA using arrays and change horizontal (category) Axis Label

1 Answers