VBA plot a Scatter graph data - X and Y axis data taken from sheet row by row
The data is time series data thus each row of X and Y data must be plotted from the respective worksheet row (20 columns of data per row)
This needs to be automated via VBA to accept the data row by row - wish to expand this with more data hence the need for VBA
Graph needs to appear like a Polygon as shown in the image I've also added some example data at the bottom of this post
Attachment 20717
Attachment 20718
See attached.Click button.Code:Sub blah()
With ActiveSheet.ChartObjects.Add(260, 80, 360, 400).Chart
.ChartType = xlXYScatterLines
For Each rw In Sheets("x").Cells(1).CurrentRegion.Rows
With .SeriesCollection.NewSeries
.XValues = rw
.Values = Sheets("Y").Cells(rw.Row, 1).Resize(, rw.Columns.Count)
End With
Next rw
End With
End Sub
Oh groan…
you've cross-posted without telling us.
Grrrr.
Have a read of http://www.excelguru.ca/content.php?184
It's a requirement to supply links to all cross-posts here and at MrExcel (and mostly all other such forums).
Quote:
Originally Posted by p45cal
Thank you very much, this is exactly what I've requested!
I've got some additions to add which ties into the principle of this plotter.
The graph is limited to 256 Series of data, I have 60,000+ to plot.
I wish to plot 1 series of data, then wait a 0.5 milli second delay then overwrite with the next row of x-y co-ordinates to make almost a real time video of the data of which I wish to visualised alongside the row number (possibly export as video?) and do away with the Legend.
Thanks P45cal for your proficient help
I was unaware of the forums being linked together, point taken for future!
The forums are NOT linked together. That's a job for you to do; to provide links to where you have cross-posted the same question.
It's a requirement here AND at MrExcel (and most other sites you might have cross-posted to). Do read that link I provided earlier (http://www.excelguru.ca/content.php?184).
Being a stickler for the rules, I have prepared a solution for you but I won't attach the file until you've provided links everywhere they're required.
Now to you problem.
0.5 millisecond delay! That's 2000 charts per second! I doubt any video would capture all the charts. I doubt Excel could display at that rate. It would be difficult to code for such a delay, so I've come up with a work-around of sorts:
In the code there's a line:
For i = 1 To 1000: DoEvents: Next i
where you should change the 1000 to suit yourself. The bigger the number, the longer the delay between charts. I leave it to you to experiment.
Other functionality:
- If you select a cell amongst the data on the left, that row's data will be charted. Use the arrow keys on the keyboard, if you want, to move up and down the data - the chart will follow.
- If you use the scroll bar, it will update the chart.
- If you press the Start button it will begin running through the rows starting from the row number in the chart's title.
- Pressing the Stop button will stop the run.
Attachment 20728
The post is also available at: mrexcel.com/forum/excel-questions/1028188-vba-plot-scatter-graph-data-x-y-axis-data-taken-sheet-row-row-data-provided.html , I haven't got 5 post yet so cannot post the full website link.
To follow up on your question, regarding play back, I would be happy for the data to skip 20 frames of data in order to maintain the overall realtime playback (time internal is 0.5 milliseconds). I would like the graph to update whilst the macro is running so it appears like a video.
Here is my modified code which overwrites the original data to plot the single Series, after a delay of 1 milli second as an example..
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If
Sub blah()
Application.ScreenUpdating = True
ActiveSheet.ChartObjects.Delete
With ActiveSheet.ChartObjects.Add(5, 5, 300, 400).Chart
.ChartType = xlXYScatterLines
.HasLegend = False
.SeriesCollection.NewSeries
For Each rw In Sheets("x").Cells(1).CurrentRegion.Rows
With .SeriesCollection(1)
.XValues = rw
.Values = Sheets("Y").Cells(rw.Row, 1).Resize(, rw.Columns.Count)
End With
Sleep (1)
Next rw
End With
End Sub
That's fine. You need to link at MrExcel to here too.Quote:
Originally Posted by aqibi2000
See attached.
I'll look at your other suggestions in a while.
When I ran your snippet with 80 rows of data, there wasn't any time difference in execution of the whole macro until Sleep with a value of 8 was used. Until then, there was no significant difference in execution time from when that line was commented out altogether. Granted mine's not the newest machine on the block.
I also needed either to put an Application.screenupdating =True or a DoEvents within the loop, else the charts didn't update visually.
So with those I couldn't get faster than 1 chart per 40 milliseconds.
Code:Set myrng = Sheets("x").Cells(1).CurrentRegion
For rw = 1 To myrng.Rows.Count Step 20
With .SeriesCollection(1)
.XValues = myrng.Rows(rw)
.Values = Sheets("Y").Cells(rw, 1).Resize(, myrng.Columns.Count)
End With
Sleep (200)
Application.ScreenUpdating = True
'DoEvents
Next rw
Thank you for this, it works wonderfully.
The final use will utilise 2 sets of X,Y Co-ordinates, would it be possible to add the additional series please?
With regards to the delay, I don't know how to over come this, my maximum allowable tolerance is 20ms :/
Supply a workbook with some (close to) real data and where it is in the workbook. You want 2 lines on the same chart?Quote:
Originally Posted by aqibi2000
Why? What's this for, ultimately?Quote:
Originally Posted by aqibi2000
Quote:
Originally Posted by p45cal
1) Correct, 2 lines using 2 separate X-Y data but of the same structure and style. I will try to locate some actual data but it's of the same nature as the original one in my first post.
2) The data is captured every 0.5 milli seconds, but is updated from the calculation system every 20ms, hence the 20ms limit will allow me to see any points of changes to the data if any occured.
This is data captured from MATLAB which needs to be visualised outside of that software, with regards to data captured via sensors.
Here is some exemplar data as requested.
There is 0.2 seconds of data (400 rows) which has values of 0 at the beginning, which I also wish to keep.
Both sets of data will contain the same number of rows, except the fact that the data will be independent.
Column A on all the sheets is the time stamp.
Hope this helps. Thanks
In the attached chart with 2 plots. Row plotted is dependent on the value in V1 of Sheet5, which is dependent on the selection on that sheet.
I suspect this data isn't very cose to real data! Both plots are always bang on top of each other.
The chart has 2 sets of axes. Only one vertical one showing, since they're both always 0 to 80.
This is intriguing; what are you looking to do, what are you looking for?
Re:"will allow me to see any points of changes to the data if any occured", I strongly suspect you can find these changes with some relatively simple arithmetic.
If you don't want to answer this in the publc domain you can always Private Message me at this site.
Thank you for the update, the data is actual and taken from the system, it's the processed road analysis data and is limited within the range of 0-80.
They may appear to be currently overlapping however in perfect form the value is +1.75 and -1.75, occurrences of defects creates the variations.
The current plot which you created works well, I've just changed the axis limits to my requirements as per photo, I do require the Start - Stop button to be enabled as well as the scroll (although it's limited to a maximum of 30k) which you added in the previous revision (which allowed to alter the value of Cell V1 quickly and allowed up and down arrow use to move sequentially).
You're work is superb nonetheless!
I would appreciate if I don't disclose specifics of the data itself, but the visualisation in the photo is exactly as wanted :)
I may have implemented much of the above request :)
see attached.
Super!