Soul777Toast
03-12-2008, 02:39 PM
Hi all,
I'm writing a function to add charts to a spreadsheet based on some user entered parameters, and one of the things I'd like it to do is to be able to handle data columns that are longer than 32000 values. To do this, I am automating a process to create a new series for each 32000 values in a column (which I have got working perfectly). What I am struggling with is being able to set the color formatting for the concurrent series in a column to be the same as the first series, and to create the new series without creating a new entry in the legend. I'm also having some trouble getting it to create the chart as a new sheet (currently it is an object in the active sheet, when I put in the
newchart.chart.location where:=xlLocationAsNewSheet line I get a new chart in a new sheet, but it's just a white blank).
Here's my current code:
Function CreateChart(Title As String, ChartData As Range, XMin As Long, XMax As Long, _
YMin As Long, YMax As Long)
Dim NewChart As ChartObject
Dim iColumn As Long
' add the chart
Set NewChart = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)
With NewChart.Chart
' make an XY chart
.ChartType = xlXYScatterSmoothNoMarkers
' add series from selected range, column by column
If ChartData.Rows.Count <= 32000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(ChartData.Rows.Count, 1))
.Name = ChartData.Cells(1, iColumn)
End With
Next iColumn
ElseIf ChartData.Rows.Count > 32000 And ChartData.Rows.Count <= 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(32001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
ElseIf ChartData.Rows.Count > 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(64000, iColumn))
.XValues = ChartData.Range("A32001:A64000")
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(64001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(64001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
End If
End With
With NewChart.Chart.Axes(xlCategory)
.MinimumScale = XMin
.MaximumScale = XMax
End With
With NewChart.Chart.Axes(xlValue)
.MinimumScale = YMin
.MaximumScale = YMax
End With
End Function
Any ideas?
I'm writing a function to add charts to a spreadsheet based on some user entered parameters, and one of the things I'd like it to do is to be able to handle data columns that are longer than 32000 values. To do this, I am automating a process to create a new series for each 32000 values in a column (which I have got working perfectly). What I am struggling with is being able to set the color formatting for the concurrent series in a column to be the same as the first series, and to create the new series without creating a new entry in the legend. I'm also having some trouble getting it to create the chart as a new sheet (currently it is an object in the active sheet, when I put in the
newchart.chart.location where:=xlLocationAsNewSheet line I get a new chart in a new sheet, but it's just a white blank).
Here's my current code:
Function CreateChart(Title As String, ChartData As Range, XMin As Long, XMax As Long, _
YMin As Long, YMax As Long)
Dim NewChart As ChartObject
Dim iColumn As Long
' add the chart
Set NewChart = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)
With NewChart.Chart
' make an XY chart
.ChartType = xlXYScatterSmoothNoMarkers
' add series from selected range, column by column
If ChartData.Rows.Count <= 32000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(ChartData.Rows.Count, 1))
.Name = ChartData.Cells(1, iColumn)
End With
Next iColumn
ElseIf ChartData.Rows.Count > 32000 And ChartData.Rows.Count <= 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(32001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
ElseIf ChartData.Rows.Count > 64000 Then
For iColumn = 2 To ChartData.Columns.Count
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(2, iColumn), ChartData.Cells(32000, iColumn))
.XValues = ChartData.Range(ChartData.Cells(2, 1), ChartData.Cells(32000, 1))
.Name = ChartData.Cells(1, iColumn)
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(32001, iColumn), ChartData.Cells(64000, iColumn))
.XValues = ChartData.Range("A32001:A64000")
End With
With .SeriesCollection.NewSeries
.Values = ChartData.Range(ChartData.Cells(64001, iColumn), ChartData.Cells(ChartData.Rows.Count, iColumn))
.XValues = ChartData.Range(ChartData.Cells(64001, 1), ChartData.Cells(ChartData.Rows.Count, 1))
End With
Next iColumn
End If
End With
With NewChart.Chart.Axes(xlCategory)
.MinimumScale = XMin
.MaximumScale = XMax
End With
With NewChart.Chart.Axes(xlValue)
.MinimumScale = YMin
.MaximumScale = YMax
End With
End Function
Any ideas?