Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using arrays in Excel Charts from VBA
Found several similar threads but nothing that seems to address thi problem. If I copy an Excel range into a variant, varArray and then use .SeriesCollection.Values = varArray I get Runtime 1004 error "Unable t set the Values property of the Series class". I've tried first copyin the 2D variant into a 1D double or variant to get a 1D array but sam message results. However if I just populate varArray locally with random numbers i works okay. Also I've found that what I originally wanted to do work up to 14 elements in varArray but fails when I increase the size t 15? Examples of what work and what doesn't below - any guidance muc appreciated. (I know I can just use Ranges in the chart but I woul prefer not to have to.) Fails: varT = Range("T").Offset(1, 0).Resize(, 1).Value ReDim dT(1 To 15) ReDim dX(1 To 15) For i = 1 To 15 dT(i) = varT(i, 1) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart .Name = sChart .ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX ..... Works: varT = Range("T").Offset(1, 0).Resize(, 1).Value ReDim dT(1 To 14) ReDim dX(1 To 14) For i = 1 To 14 dT(i) = varT(i, 1) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart .Name = sChart .ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX ..... Works: ReDim dT(1 To 50) ReDim dX(1 To 50) For i = 1 To 50 dT(i) = CDbl(i/2) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart .Name = sChart .ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX .... -- Chris_Hawkin ----------------------------------------------------------------------- Chris_Hawkins's Profile: http://www.excelforum.com/member.php...fo&userid=2692 View this thread: http://www.excelforum.com/showthread.php?threadid=40145 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using arrays in Excel Charts from VBA
The chart SERIES formula has a limit of (just under) 255 characters for
its various components. When you try and set the chart Values (or XValues) to an array, that limit is easily exceeded. Put the data in a worksheet range and use that range in the chart series. You can also use a named formula that contains a series of constant values. However, there are two variants to this and the instinctive one has a limit of 256 elements. All the various options are shown in code samples below. You must select an activechart with at least 1 plotted series before running any of the procedures. The first creates a chart from an array. The limit is a little under 255 characters. For integers it translates to 87 values 1..87. Sub ChartSeriesLengthOK() Dim i As Integer, x() As Integer ReDim x(1 To 50) For i = LBound(x) To UBound(x): x(i) = i: Next i ActiveChart.SeriesCollection(1).Values = x End Sub The next demonstrates an approach that will fail because the length of the SERIES formula becomes too long. Sub ChartSeriesTooLong() Dim i As Integer, x() As Integer ReDim x(1 To 100) For i = LBound(x) To UBound(x): x(i) = i: Next i 'I believe the threshold is 87 ActiveChart.SeriesCollection(1).Values = x End Sub The next one uses a Named formula. The first approach most people are likely to try works up to 256 elements. The interesting thing is that if one replaced the 100 in the code below with 1000 there will be no error or warning. However, the chart series will have only 256 elements in it. Sub ChartSeriesWithName() Dim i As Integer, x() As Integer ReDim x(1 To 100) For i = LBound(x) To UBound(x): x(i) = i: Next i ActiveWorkbook.Names.Add Name:="abc", _ RefersTo:=x ActiveChart.SeriesCollection(1).Values = _ "='" & ActiveWorkbook.Name & "'!abc" End Sub I assume the 256 limit has something to do with the max. number of columns. Using a 2D array as below removes that limitation and the chart contains 1000 elements. Sub ChartSeriesWith2DArrayAndName() Dim i As Integer, x() As Integer ReDim x(1 To 1000, 1 To 1) For i = LBound(x) To UBound(x): x(i, 1) = i: Next i ActiveWorkbook.Names.Add Name:="abc", _ RefersTo:=x ActiveChart.SeriesCollection(1).Values = _ "='" & ActiveWorkbook.Name & "'!abc" End Sub The final approach adds a worksheet that is immediately hidden. That worksheet is populated with the data in the array and the chart refers to that range. Sub ChartSeriesFromRange() Dim i As Integer, x() As Integer, WS As Worksheet, _ aChart As Chart, TargCells As Range ReDim x(1 To 1000) For i = LBound(x) To UBound(x): x(i) = i: Next i Set aChart = ActiveChart Set WS = ActiveWorkbook.Worksheets.Add WS.Visible = xlSheetHidden '**** optionally xlSheetVeryHidden Set TargCells = WS.Cells(1, 1). _ Resize(UBound(x) - LBound(x) + 1, 1) TargCells.Value = Application.WorksheetFunction.Transpose(x) aChart.SeriesCollection(1).Values = TargCells End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <Chris_Hawkins.1uqiuh_1125669942.5627@excelforum- nospam.com, says... Found several similar threads but nothing that seems to address this problem. If I copy an Excel range into a variant, varArray and then use SeriesCollection.Values = varArray I get Runtime 1004 error "Unable to set the Values property of the Series class". I've tried first copying the 2D variant into a 1D double or variant to get a 1D array but same message results. However if I just populate varArray locally with random numbers it works okay. Also I've found that what I originally wanted to do works up to 14 elements in varArray but fails when I increase the size to 15? Examples of what work and what doesn't below - any guidance much appreciated. (I know I can just use Ranges in the chart but I would prefer not to have to.) Fails: varT = Range("T").Offset(1, 0).Resize(, 1).Value ReDim dT(1 To 15) ReDim dX(1 To 15) For i = 1 To 15 dT(i) = varT(i, 1) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart Name = sChart ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX .... Works: varT = Range("T").Offset(1, 0).Resize(, 1).Value ReDim dT(1 To 14) ReDim dX(1 To 14) For i = 1 To 14 dT(i) = varT(i, 1) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart Name = sChart ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX .... Works: ReDim dT(1 To 50) ReDim dX(1 To 50) For i = 1 To 50 dT(i) = CDbl(i/2) dX(i) = i Next i varT = dT varX = dX Set TheChart = Charts.Add With TheChart Name = sChart ChartType = xlXYScatterLines Set TheSeries = .SeriesCollection.NewSeries TheSeries.Values = varT TheSeries.XValues = varX .... -- Chris_Hawkins ------------------------------------------------------------------------ Chris_Hawkins's Profile: http://www.excelforum.com/member.php...o&userid=26924 View this thread: http://www.excelforum.com/showthread...hreadid=401454 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Charts - Text Box Problem | Charts and Charting in Excel | |||
Problem with SUMPRODUCT and Arrays | Excel Worksheet Functions | |||
Making charts from arrays in VBA | Charts and Charting in Excel | |||
VBA (arrays problem) | Excel Programming | |||
Elusive Problem with Arrays | Excel Programming |