creating a scatter plot containing series dynamically using VBA in microsoft excel

Question

I am rather new to VBA so I would appreciate more thorough explanations of what you did to solve this minor issue.

I am tasked with creating a simple VBA program that takes an already sorted set of data (I learned about it and then used a VBA program to sort my data alphabetically already based on a particular column's values) and does the following two things with it:

Graphs a scatter plot with it (either within that sheet or in a separate sheet)

AND

Creates series with that scatter plot dynamically (meaning that scatter plot has series on it based on a particular column's value). I will not know how many series I need but I do know that since it's sorted, the column defining the type of data in that row will be in alphabetical order (and I assume the VBA program can first create a series with the first row's name and then not create more series until it finds a different name in that column until it is finished graphing the data)

an example of a simple 3 column table is shown below:

SERIES NAME ___X VALUE_____Y VALUE

A__________________1___________1

A__________________2___________2

A__________________3___________3

A__________________4___________4

B__________________5___________5

B__________________6___________6

B__________________7___________7

C__________________8___________8

C__________________9___________9

C__________________1___________1

(there could be more rows and more unique series names of course... )

so in this example, the graph is already sorted and I would like there to be a scatter plot with 3 series on it (A being the 1st, B being the 2nd, and 3 being the 3rd)

I have the code to create a scatter plot with one series so far but I've been stuck trying to figure this out (code shown below). Any help with explanation is greatly appreciated :D

Here's my code so far (without the dynamic series part ofc)

Sub creatingmyscatterplot()

'Dim aRng As Range
'Dim seriescheck As Range
Dim Chart1 As Chart
Set Chart1 = Charts.Add

'Set aRng = Selection.CurrentRegion
'Set aRng = aRng.Offset(1, 0).Resize(aRng.Rows.Count - 1)
'Set seriescheck = aRng.Resize(aRng.Rows.Count, 1)

'Dim seriesName As String, seriesData As Range
'These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


With Chart1

    .ChartType = xlXYScatterLines
    .SeriesCollection.NewSeries

    'This creates the graph
    .SeriesCollection(1).Name = "=Sheet1!$A$2"
    .SeriesCollection(1).XValues = "=Sheet1!$B$2:$B$26001"
    .SeriesCollection(1).Values = "=Sheet1!$C$2:$C$26001"


    'Titles
    .HasTitle = True
    .ChartTitle.Characters.Text = "X vs. Y"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "SOME TEXT"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "SOME TEXT AS WELL"
    .Axes(xlCategory).HasMajorGridlines = True

    'Formatting
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = False
    .HasLegend = False
    .Axes(xlValue).MaximumScale = 100
    .Axes(xlValue).MinimumScale = 0

End With


'These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sorry about the indentation of the first and last lines. I just wanted those lines to be in the code block instead of outside it.

THANKS :D

Dy.Lee Dy.Lee · Accepted Answer · 2017-10-06T01:22:54

After collect range by your series name, and add series.

Sub creatingmyscatterplot()
Dim rngData() As Range, rngDB As Range
Dim Ws As Worksheet
Dim i As Long, n As Long

Set Ws = Sheets(1)
    With Ws
        Set rngDB = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
    End With
    'By same value area, set rngData() array
    n = 1
    ReDim Preserve rngData(1 To n) 'dynamic array
    For i = 1 To rngDB.Rows.Count
        If rngData(n) Is Nothing Then
           Set rngData(n) = rngDB(i)
        Else
            Set rngData(n) = Union(rngData(n), rngDB(i))
        End If
        If rngDB(i) <> rngDB(i + 1) Then
            n = n + 1
            ReDim Preserve rngData(1 To n)
        End If
    Next i

'Dim aRng As Range
'Dim seriescheck As Range
Dim Chart1 As Chart
Set Chart1 = Charts.Add

'Set aRng = Selection.CurrentRegion
'Set aRng = aRng.Offset(1, 0).Resize(aRng.Rows.Count - 1)
'Set seriescheck = aRng.Resize(aRng.Rows.Count, 1)

'Dim seriesName As String, seriesData As Range
'These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


With Chart1

    .ChartType = xlXYScatterLines
    'if your activecel in data range, series is created automatically Unintentionally. So,all series are to be deleted
    For i = .SeriesCollection.Count To 1 Step -1
        .SeriesCollection(i).Delete
    Next i
    'This creates the graph
    For i = 1 To n - 1 'useful rngData()'s count is n -1
        .SeriesCollection.NewSeries
        .SeriesCollection(i).Name = rngData(i)(1)
        .SeriesCollection(i).XValues = rngData(i).Offset(, 1)
        .SeriesCollection(i).Values = rngData(i).Offset(, 2)
    Next i

    'Titles
    .HasTitle = True
    .ChartTitle.Characters.Text = "X vs. Y"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "SOME TEXT"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "SOME TEXT AS WELL"
    .Axes(xlCategory).HasMajorGridlines = True

    'Formatting
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = False
    .HasLegend = False
    .Axes(xlValue).MaximumScale = 100
    .Axes(xlValue).MinimumScale = 0

End With


'These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

creating a scatter plot containing series dynamically using VBA in microsoft excel

1 Answers