Creating multiple graphs from a large data set using VBA looping

Question

I am trying to create a macro in VBA that will take a large data set in Sheet1 (called Raw Data) and create a XY scatter plot for every 8000 data points in another worksheet. The macro will also need to label each graph with what range it represents (ie 1-8000, 8001-16000 etc).

The large data set consists of temperature readings from 8 different thermocouples which record data every second. The number of data points will vary based on how long the experiment was run. The temperature values are stored in columns C through J and the time parameter is in column T.

What I have right now is a "batch" approach where the macro is set up to graph data in chunks of 8000 up to 32000 (4 different plots). This approach is not practical because the data set will almost always be significantly larger than 32000 points.

What I would like the macro to do is automatically graph and label every 8000 data points until there is no more data to graph.

I have been looking into using a loop but I am new to writing code and not sure how.

Any suggestions or help is greatly appreciated!

Here's some of my batch code:

'creates graph for first 8000 seconds in TC 1

Sheets("TC 1").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Raw Data'!$C$1"
ActiveChart.SeriesCollection(1).XValues = "='Raw Data'!$t$2:$t$8000"
ActiveChart.SeriesCollection(1).Values = "='Raw Data'!$C$2:$C$8000"

With ActiveChart

'X axis name
.axes(xlCategory, xlPrimary).HasTitle = True
.axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (seconds)"
'y-axis name
.axes(xlValue, xlPrimary).HasTitle = True
.axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature (F)"

'chart title
.HasTitle = True
.ChartTitle.Text = ("1-8000 seconds")
'adjusts the size/placement of graph and x-axis values
 Set RngToCover = ActiveSheet.Range("A1:T25")
 Set ChtOb = ActiveChart.Parent
 ChtOb.Height = RngToCover.Height ' resize
 ChtOb.Width = RngToCover.Width ' resize
 ChtOb.Top = RngToCover.Top ' repositon
 ChtOb.Left = RngToCover.Left ' reposition
 ActiveChart.axes(xlCategory).Select
 ActiveChart.axes(xlCategory).MinimumScale = 0
 ActiveChart.axes(xlCategory).MaximumScale = 8000

End With

user3746074 · Accepted Answer · 2014-06-18 15:44:19Z

Here is what I came up with.

The macro calculates the total number of used rows, then divides that number by 8000.

The For...Next loop runs from 0 to the total rows divided by 8000.

Dim i As Integer
Dim j As Variant
Dim p As Integer
Dim start_row As Long
Dim end_row As Long
Dim RngToCover As Range
Dim ChtOb As ChartObject

i = Worksheets("Raw Data").UsedRange.Rows.Count
j = i / 8000

Sheets("TC 1").Activate

For p = 0 To j

start_row = (p * 8000) + 2
end_row = ((p + 1) * 8000) + 1

Set ChtOb = ActiveSheet.ChartObjects.Add(Left:=20, Width:=800, Top:=20, Height:=250)

ChtOb.Chart.ChartType = xlXYScatterSmoothNoMarkers
ChtOb.Activate

With ActiveChart.SeriesCollection.NewSeries
    .Name = Worksheets("Raw Data").Cells(1, 3)
    .XValues = Worksheets("Raw Data").Range(Worksheets("Raw Data").Cells(start_row, 20), Worksheets("Raw Data").Cells(end_row, 20))
    .Values = Worksheets("Raw Data").Range(Worksheets("Raw Data").Cells(start_row, 3), Worksheets("Raw Data").Cells(end_row, 3))

    End With
    Next

Mark Balhoff · Accepted Answer · 2014-06-16 23:31:48Z

0

It sounds like you already understand how to generate the charts for a given 8000 records. Below is a WHILE loop to keep running your export code until it finds an empty cell in the source column for the X-axis (column T).

Dim i As Integer
Dim ws As Worksheet
i = 2
Set ws = ThisWorkbook.Worksheets("Raw Data")
While ws.Cells(i, 20).Value <> ""
    ''' Create Chart for Next Data Set Starting at Row i  (up to 8000 records)
    i = i + 8000
Wend

answered Jun 16, 2014 at 23:31

Mark Balhoff

2,3644 gold badges24 silver badges31 bronze badges

Collectives™ on Stack Overflow

Creating multiple graphs from a large data set using VBA looping

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related