1

I am trying to create a chart that plots X and Y points, but draws a line between each X point for each series. Sort of like this:

enter image description here

However my issue is how I define my Xvalues and Values in the SeriesCollection of VBA. This is because my values are actually stored in arrays. I have used arrays because I have data that is:

ID  Age1    Age2    Per
1   21      22      54.2%
2   19      23      68.6%
3   18      23      42.0%
4   30      33      45.1%
5   17      19      33.0%
6   19      22      41.3%
7   22      27      20.4%
8   19      20      56.4%
9   30      33      42.8%
10  21      22      59.7%

So I need to plot on the X-axis the value in Age1 and Age2. The corresponding Y value is in Per column. But I need to create a new series for each ID. I have the following code and tagged where the issue is. Note it does not compile. I think the issue is how I pass through the values to create the XY chart. I only want to pass through the values onto the chart.

Sub name()
    Dim age1 As Variant
    Dim age2 As Variant
    Dim per1 As Variant
    Dim per2 As Variant
    Dim id As Variant
    Dim ln As Integer
    Dim cht As Chart
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim xdata As Varaint
    Dim ydata As Varaint

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)

    id = ws.Range("A2", Range("A2").End(xlDown)).Value
    age1 = ws.Range("B2", Range("B2").End(xlDown)).Value
    age2 = ws.Range("C2", Range("C2").End(xlDown)).Value
    per1 = ws.Range("D2", Range("D2").End(xlDown)).Value
    per2 = ws.Range("D2", Range("D2").End(xlDown)).Value
    ln = UBound(id) - LBound(id) + 1

    Set cht = ws.ChartObjects("chart").Chart
        With cht
            .ChartArea.ClearContents
            .ChartType = xlXYScatter

            For i = 1 To ln
                xdata=Array(age1(i),age2(i)) 'I assumed this would get the values in age1(i) and age2(i) and plot those as the xdata
                ydata=Array(per1(i),per2(i)) 'Same error as above, but would use ydata
                .SeriesCollection.NewSeries
                .SeriesCollection(i).XValues = xdata
                .SeriesCollection(i).Values = ydata
                .SeriesCollection(i).Name = id(i)
            Next i
        End With

End Sub
Improve this question
6
  • If you're getting a runtime error, then it absolutely does compile. If you're getting a compile error, that's something different. What's the specific error and what line causes it? Commented Aug 24, 2018 at 15:00
  • Note also: You've declared xdata and ydata as Range objects, but you've never instantiated them as ranges. There may be further problems with the actual assignment statement xdata = ... but you can't put a value in a Nothing, and that's what an un-instantiated range object is :) Commented Aug 24, 2018 at 15:01
  • I re-defined xdata and ydata as Variants. The compile error is Syntax error. On the line with the comments Commented Aug 24, 2018 at 15:04
  • syntax error because (age1(i),age2(i)) is not valid notation of anything in VBA. I think this is what you meant: xdata = Array(age1(i), age2(i)) but without testing it I'm pretty sure that won't work, because it looks like you're trying to put an array of both age data into a single series XValues. Each series must have its own XValues. Commented Aug 24, 2018 at 15:08
  • Edited code using your suggestion, still says subscript out of range. But now it compiles. Commented Aug 24, 2018 at 15:12

1 Answer 1

Reset to default
2

The issue is how you call the parts of an Array. The age1 array, along with the other arrays, are TWO DIMENSIONAL. So instead of age1(i) you want age1(i,1) such that i is the row and 1 is the column.

Dim xdata As Variant
Dim ydata As Variant

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

id = Range(Range("A2"), Range("A2").End(xlDown)).Value2
age1 = Range(Range("B2"), Range("B2").End(xlDown)).Value2
age2 = Range(Range("C2"), Range("C2").End(xlDown)).Value2
per1 = Range(Range("D2"), Range("D2").End(xlDown)).Value2
per2 = Range(Range("D2"), Range("D2").End(xlDown)).Value2
ln = UBound(id) - LBound(id) + 1

Set cht = ws.ChartObjects("chart").Chart
    With cht
        .ChartArea.ClearContents
        .ChartType = xlXYScatterLines
        For i = 1 To ln
         xdata = Array(age1(i, 1), age2(i, 1))
         ydata = Array(per1(i, 1), per2(i, 1))
            .SeriesCollection.NewSeries
            .SeriesCollection(i).XValues = xdata
            .SeriesCollection(i).Values = ydata
            .SeriesCollection(i).Name = id(i, 1)
        Next i
    End With
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Good work, yes range.Value always returns a 2-dimensional array.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.