creating chart with more than 32000 data values

B

blueflash

New Member
Joined
May 23, 2012
Messages
37
I have asked this in another forum without any replies - I am hoping for success here please.

I have greater than 130,000 data points I want to plot on a scatter plot in Excel 2007 using vba.
The data points are in a worksheet "Data" in columns 2 and 6 and I have produced subsets of 32000 values in columns 15,16 and 17,18 and 19,20 etc

All data in columns 2,15,17 are sorted in increasing values.

The code I use to plot these points in chart "cname" is:
Code: 
[COLOR=#333333]Sheets("Data").Select[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit;">    Set data_range1 = Range(Cells(2, 2), Cells(32001, 2))
   Set data_range2 = Range(Cells(2, 6), Cells(32001, 6))    
   Sheets(cname).Select    
   ActiveChart.SeriesCollection(1).XValues = data_range1
   ActiveChart.SeriesCollection(1).Values = data_range2 
   If n_subsets > 0 Then
        kk = 0
        For ll = 1 To n_subsets
            Sheets("Data").Select
            Set data_range1 = Range(Cells(2, 15 + kk), Cells(32001, 15 + kk))
            Set data_range2 = Range(Cells(2, 16 + kk), Cells(32001, 16 + kk))
            Sheets(cname).Select
            ActiveChart.SeriesCollection(1).XValues = data_range1
            ActiveChart.SeriesCollection(1).Values = data_range2
            kk = kk + 2
        Next ll </code>[COLOR=#333333]   
 End If
[/COLOR]


When I step through this code, I can see the first subset of data plots ok then when I get to the second subset, it plots ok but as soon as it plots, the first data set disappears. Similarly with subsequent data sets, only the last plotted data set remains.

Can any one tell me what is happening? Are sequential data sets being plotted as different series and then becoming invisible?

If I try and create the plot manually, I can add the data sets ok but they are added as separate series but that is ok, I can change the format of the points and they all look like they are part of the one series.

So how do I do this in VBA?

Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Each time your loop cycles, it's resetting the chart's data set.
That's why the previous series disappears.

Untested of course, but I think you need to Union your ranges and then set the series.
Code: 
	Sheets("Data").Select
	Set data_range1 = Range(Cells(2, 2), Cells(32001, 2))
	Set data_range2 = Range(Cells(2, 6), Cells(32001, 6))    


	If n_subsets > 0 Then
        kk = 0
        For ll = 1 To n_subsets
            
            Set data_range1 = Application.Union(data_range1, Range(Cells(2, 15 + kk), Cells(32001, 15 + kk)))
            Set data_range2 = Application.Union(data_range2, Range(Cells(2, 16 + kk), Cells(32001, 16 + kk)))


            kk = kk + 2
        Next ll    
	End If			
	
            Sheets(cname).Select
            ActiveChart.SeriesCollection(1).XValues = data_range1
            ActiveChart.SeriesCollection(1).Values = data_range2
 
Upvote 0
Thanks for your reply.
I tried your suggestion but when I got to the last line

Code: 
[COLOR=#574123]ActiveChart.SeriesCollection(1).Values = data_range2[/COLOR]

I got a runtime error 1044 Application-defined or object-defined error.
I think that might be due to excel only being able to handle 32000 points in a series??
That being so, my code wouldn't work either.

I think I can add the data as a number of series on one chart which will then all plot ok.

Then my next question is, how can I select all the series on a plot and change the characteristics of each plotted point to be a black diamond 3 units big?
I don't know how to change the properties of a number of series.
 
Upvote 0
I think this gets through the gyrations....

Code: 
Sub Foo()
    'Declares
    Dim data_range1 As Range
    Dim data_range2 As Range
    Dim chrt As Chart
    Dim chrtsrs As Series


    'constants
    Const cname = "Chart3"
    Const n_subsets = 3


    Sheets("Data").Select
    With Sheets("Data")
        Set data_range1 = .Range(.Cells(2, 2), .Cells(32001, 2))
        Set data_range2 = .Range(.Cells(2, 6), .Cells(32001, 6))
    End With


    Sheets(cname).Select
    Set chrt = Charts(cname)


    With chrt
        .Select


        ' Remove any series existing within the chart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop


        'Add New Series and populate
        Set chrtsrs = chrt.SeriesCollection.NewSeries
        chrtsrs.Name = "Series0"
        chrtsrs.XValues = data_range1
        chrtsrs.Values = data_range2


    End With


'[COLOR=#574123]can add the data as a number of series on one chart which will then all plot ok[/COLOR]
    If n_subsets > 0 Then
        kk = 0
        For ll = 1 To n_subsets
            With Sheets("Data")
                Set data_range1 = .Range(.Cells(2, 15 + kk), .Cells(32001, 15 + kk))
                Set data_range2 = .Range(.Cells(2, 16 + kk), .Cells(32001, 16 + kk))
            End With


            'Add New Series and populate
            Set chrtsrs = ActiveChart.SeriesCollection.NewSeries


            With chrtsrs
                .Name = "Series" & ll
                .Values = data_range2
                .XValues = data_range1
            End With
            kk = kk + 2
        Next ll
    End If


'[COLOR=#574123]how can I select all the series on a plot and change the characteristics of each plotted point to be a black diamond 3 units big?[/COLOR]
    'Apply Formatting
    With chrt
        For i = 1 To .SeriesCollection.Count
            With .SeriesCollection(i)
                .MarkerSize = 3
                .MarkerStyle = xlMarkerStyleDiamond
                r = WorksheetFunction.RandBetween(0, 255)
                g = WorksheetFunction.RandBetween(0, 255)
                b = WorksheetFunction.RandBetween(0, 255)
                .MarkerForegroundColor = RGB(r, g, b)
            End With
        Next
    End With
End Sub
 
Upvote 0
Many thanks tweedle, that allows me to do exactly what I want. I can now sleep at nights again.




tweedle said:
I think this gets through the gyrations....

Code: 
Sub Foo()
    'Declares
    Dim data_range1 As Range
    Dim data_range2 As Range
    Dim chrt As Chart
    Dim chrtsrs As Series


    'constants
    Const cname = "Chart3"
    Const n_subsets = 3


    Sheets("Data").Select
    With Sheets("Data")
        Set data_range1 = .Range(.Cells(2, 2), .Cells(32001, 2))
        Set data_range2 = .Range(.Cells(2, 6), .Cells(32001, 6))
    End With


    Sheets(cname).Select
    Set chrt = Charts(cname)


    With chrt
        .Select


        ' Remove any series existing within the chart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop


        'Add New Series and populate
        Set chrtsrs = chrt.SeriesCollection.NewSeries
        chrtsrs.Name = "Series0"
        chrtsrs.XValues = data_range1
        chrtsrs.Values = data_range2


    End With


'[COLOR=#574123]can add the data as a number of series on one chart which will then all plot ok[/COLOR]
    If n_subsets > 0 Then
        kk = 0
        For ll = 1 To n_subsets
            With Sheets("Data")
                Set data_range1 = .Range(.Cells(2, 15 + kk), .Cells(32001, 15 + kk))
                Set data_range2 = .Range(.Cells(2, 16 + kk), .Cells(32001, 16 + kk))
            End With


            'Add New Series and populate
            Set chrtsrs = ActiveChart.SeriesCollection.NewSeries


            With chrtsrs
                .Name = "Series" & ll
                .Values = data_range2
                .XValues = data_range1
            End With
            kk = kk + 2
        Next ll
    End If


'[COLOR=#574123]how can I select all the series on a plot and change the characteristics of each plotted point to be a black diamond 3 units big?[/COLOR]
    'Apply Formatting
    With chrt
        For i = 1 To .SeriesCollection.Count
            With .SeriesCollection(i)
                .MarkerSize = 3
                .MarkerStyle = xlMarkerStyleDiamond
                r = WorksheetFunction.RandBetween(0, 255)
                g = WorksheetFunction.RandBetween(0, 255)
                b = WorksheetFunction.RandBetween(0, 255)
                .MarkerForegroundColor = RGB(r, g, b)
            End With
        Next
    End With
End Sub
Click to expand...
 
Upvote 0
Glad we got to a happy place.
I have to admit-it was a bit of a head scratcher for a while.
What do the 130k points, if you don't mind my asking, I've never had cause to plot so much, and really, they just left a huge blob on the chart (with my test values).
 
Upvote 0
I am dealing with large data sets from geological surveys. There is usually some structure in the data which can often been seen using probability plots. If you are interested, the link

https://dl.dropbox.com/u/84570865/cum_plot.jpg

shows such a plot with about 31000 points. More data might fill in the blank areas in the top right of the plot.

Thanks again for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
396
felixstraube
F
S
  • Question Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
853
Domenic
D
I
  • Question Question
Data formatting for Chart
Replies
3
Views
655
IREALLYambatman
I
T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
136
TheTiredEngineer
T
R
  • Question Question
Replicate chart in Power BI to Excel
Replies
3
Views
207
RockandGrohl
R

Forum statistics

Threads
1,222,246
Messages
6,164,807
Members
451,917
Latest member
WEB78

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top